Returning Nth Factor Of Number Via Formula?

Oct 15, 2013

Is there a way of returning the nth factor of a number via a formula?

That is to say, if I have 10, its divisors are 1, 2, 5 and 10, and if I wanted to return the second factor, it would be 2.

View 9 Replies


ADVERTISEMENT

Smallest Prime Factor Of A Number

Apr 14, 2009

Is there any formula witch i can use to find the smallest prime factor of a number?

Example: In A1 i write 30 (30=2*3*5 (the prime factors))

in B1 i write a formula, and the result will be 2 (smallest prime factor)

What is the formula?

View 7 Replies View Related

Formula For Present Value Factor

May 22, 2007

Which formula should I use to look for the present value factor if I have the interest rate and the # of periods. attached file.

View 9 Replies View Related

Returning Number As Text In IF Formula

Oct 6, 2009

This is probably a pretty basic question, but I can't find an answer in Excel help or on the interwebs. I have an IF formula and I'd like 0 returned if the statement is false (e.g. =IF(A1<>0, A1, 0)); however, I would like the 0 returned as text. I've tried the TEXT function and using ` in different places, but all to no avail.

View 7 Replies View Related

3D Formula Returning Zero - Need Lowest Number Above Zero

Aug 1, 2014

Code:
=IF(MIN(Sheet1:Sheet3!B1)=0,SMALL(Sheet1:Sheet3!B1,2),MIN(Sheet1:Sheet3!B1))

The above formula returns a zero value if there is a zero in more than one of the B1 cells in the worksheet range

I need the lowest number above zero in all the B1 cells across all worksheets in range.

View 3 Replies View Related

Match Formula Returning Wrong Row Number

Nov 1, 2006

I have a spreadsheet, with a Match Formula, that appears not to work. It looks at cell F1, and scans through cell C2:C14, and want it to return the relevant row in Column B (My work network will not allow me to upload the file). Cell F1 is the name of the sheets within the workbook (one for every month). However my data currently only has Apr - Jun, but when I choose month May, the formula feturns Month June.

View 3 Replies View Related

Index IF Match Formula / Returning Room Number That Matches The Hours Used

Mar 21, 2014

I am trying to find a formula that populates a room number based on the number of hours used. I have a chart that is by building but multiple rooms within each.

Where I am having trouble is when there are more than one room with the same number of hours used then sometimes the formula populates the wrong room number.

Attached is my example spreadsheet of my chart. Book1.xlsx

I have shown what I need, what I have tried and a blank space to show where I need it to go.

View 3 Replies View Related

Add This Number To The Start Time, Factor In Break Minutes And Get To The Projected Completion Time

Mar 3, 2009

I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!

View 4 Replies View Related

Nested If Factor

Apr 17, 2007

I have the task of creating a spreadsheet that will track and give point value to our sales reps' daily accessory sales. The following is the point value criteria for dollars:

Sell over $30 - 1 point daily
Sell over $100 - 2 points daily
Sell over $150 - 3 points daily
Sell over $300 - 5 points daily
Sell over $500 - 6 points daily
Sell over $1,000 - 7 points daily

Each cell will have a different dollar value in it. I've tried so many different ways to do this IF factor that I have confused myself and spun in circles. Can anyone help me with this? I am about ready to pull out my hair, and really don't want to have to go in and manually do this daily for 30 reps.

View 5 Replies View Related

Rounding Up By A Factor

May 22, 2007

I am having a problem getting a number to round correctly in Excel. I know I can use =ROUNDUP(x,x) to get it to the nearest whole number but I also need the rounded number to be a factor of another number like you would use in =MROUND(x,x). The problem is the MROUND function also rounds down. As an example if I have the number 12.5 and I want this to be rounded up by a factor or 4 I would get 16.

View 8 Replies View Related

Copy Values Down By Given Factor

Feb 3, 2014

See attached file. I am looking for a macro which can copy/paste down a value (column A) by a given factor (column B). The result should be a list of all values (column C). The amount of values and factors is unknown (the attached example ends at row 5).

copy.xlsb‎

View 4 Replies View Related

Multi Factor Weighting To Delta

Feb 16, 2008

I have two factors that both change, resulting in two unique values. I'm trying to determine which portion of the resulting delta is attributable to each factor. Example:

Hourly Cost Run Rate Piece Cost (Hourly Cost / Run Rate)
$105.00 4,500 $0.0233
$126.10 3,000 $0.0420

Two unique results titled Piece Cost = Delta
$0.0420 - $0.0233 = $0.0187

How much of the $0.0187 is attributable to the change in Hourly Cost and how much of the $0.0187 is attributable to the change in Run Rate?

View 9 Replies View Related

Returning Positive Number Regardless

Jun 12, 2009

i have a list of numbers in column A and B and in column C is = B - A. But what i want is for Column C to always = positive number regardless even if say A = 10 and B = 8 the true answer is -2 but i would like the reading of 2. Is there a way to say B - A and if = - make it -( ) ? (otherwords +)

View 2 Replies View Related

How To Sum Values Each One Multiplied By A Factor Obtained Using Lookup

Jul 15, 2014

I got a list of products and a price list. How to obtain a total for a given subset of products using a formula? Kind of sumif multiplied by a vlookup...

View 4 Replies View Related

Randomly Group N Elements (where N Is Factor Of 7) Into Groups Of 7

Oct 7, 2013

I want to group N elements which is a factor of 7 into groups of 3 randomly. For example, I have 21 elements:

ZSW
PMH
NYV
JTX
DEF
CRK
AOI

I want to group them into 6 more sets. Now, all in all 7 sets. Group them into other sets without the element repeating in the row it has occupied before. For example, for letter P. "P" has occupied row 2 in set 1, it cannot occupy row 2 anymore in other sets. Therefore, each element must have occupied row 1-7 in all the sets.

See attached excel file for the example output : grouping7-final.xlsx‎

View 14 Replies View Related

=sumproduct- Returning The Number Of Items

Feb 23, 2009

Im using the below formula -


=SUMPRODUCT(--(Jan!D9:D999 > 0)--(Jan!Y9:Y999="FALSE"))
But it keeps returning the number of items in JanD9:D999 > 0 , when i want the number of items in column Y which contain "FALSE" if D is greater than 0.

View 9 Replies View Related

Returning Number From Text String

Jul 24, 2007

I have been trying a few formulas to return numbers from a string of text. The problem I am having is that there are more than one set of numbers that I need returned.

Here is an example of the text entered in F2

"K2PT bus structure [2 locations] & strain structure South West of L9L12-12 [1 location] / Use #4/0, std, cu conductor, connector Cat ID#10842 at structure leg & connector Cat ID#9655 at #4/0, std, cu gnd loop to connect #4/0, std, cu gnd loop to str leg."

What I need returned in a cell H2 is the numbers beside the Cat ID#

10842, 9655

I tried a Return Right Value formula, but I think because there are more than one possible value it isn't working properly.

View 5 Replies View Related

Calculating Adjusted Factor Based On Variable Values

Feb 7, 2014

Here is a table with values from Factor1 to Factor 5. Underneath that, for a specific index, I can mark Y or N for factor values. For instance, for index 1, Factor1 and 2 is marked as Y. The goal is to calculate total factor based on variable and factor values. In this case, variable value is 6, factor 1 =1 and factor 2=1.25). So Total factor = 6*1.25*1.5 = 11.25. If all factor values are marked as N, then total factor = variable value (Example is for Index 3)

How this can be implemented. I tried using COUNT function to count the total number of Y but that works only if all factors have same values.

Table_factors.jpg

View 4 Replies View Related

Conditionally Importing Data From One Of Several Different Worksheets Based On 1 Factor?

May 22, 2014

I am currently working on a spreadsheet that records data from 11 different locations, with each location having it's own page within the workbook.

All of the pages are identical in terms of how the data is stored, the only difference is literally the name of each page (i.e. the location).

What I would like is to have a master page that then pulls through the relevant data, dependent on which location I choose from a drop down list on this page. As each individual page is identical in terms of lay-out, one formula will work for each page, with the only obvious exception is the page reference embedded in the formula. I know how to do a standard lookup for various figures, but I can't figure out a formula that would work with 11 different options... Is there any way to accomplish this?

View 1 Replies View Related

Selecting First Negative Number In Row And Returning Associated Column Value?

Feb 10, 2014

I have row of dates and underneath those dates I have a row of numbers. The numbers count down. For example:

[Code] .....

At the end of the row I want a cell to return the date that the row first started going negative at. In this example, it should display 10/31/2014 (for when it went to -60).

View 5 Replies View Related

Returning A Number Of Characters From A Variable String

Jun 4, 2014

If for example, I have a set of IDs like:

Bellen-234-B25R
Carlota-345-C20RDF
Quad-765-Q20LP

How would I return just the first 4 characters after the second dash? The closest I can get is ALL the characters after the second dash.

View 4 Replies View Related

2007, Weeknum() Returning Incorrect Number

Aug 12, 2009

I need to use weeknum() for a number of situations in a project I'm working on. But when I use =WEEKNUM(DATE(2010;1;31)) it returns 6, instead of 4 as it should. Using it on today's date returns correct number.

View 3 Replies View Related

Returning Row / Column Number Of Cell That Contains Particular Text

Jul 16, 2014

I am looking to write a formula that will return the row or column number of the cell that contains a particular text within the string. I know it can be done with script, but need it as a formula. Also, to add another level of difficulty, I'd like it to be a traditional formula, no special keystroke to activate. It will be used within a much larger formula.Ex. If G5 has the string "CURRENT DATE" in it, and I look for "DATE", the formula would return 7 for the column or 5 for the row.

View 4 Replies View Related

Returning A List Of Names Without The Prefixed Number

Jul 8, 2009

I have a list of names in say column A, but the names are prefixed with numbers. So, for example you have 2 Peter Jones, 5 Johnny Piper, 10 Andrea, 3 St. Mary's, 13 White etc.

I want to return these list of names in another column say B, but without the prefixed numbers. So, column B will have Peter Jones, Johnny Piper, Andrea, St. Mary's, White etc.

View 9 Replies View Related

QueryTable Not Returning Correct Number Format

Jul 28, 2006

I have a querytable running on my sql database. In the query I have a trimleft function because the numbers are stored with several proceeding blank spaces. When I execute the query through my macro, everything works fine, except I can't average or sum the results. They can be multiplied, divided, etc, but not averaged or summed (they just show as zero on summing, which makes the average DIV/0). The really strange part is that if I select a number and go to the formula bar and hit delete at the beginning of the number (which actually deletes nothing), the number is suddenly capable of actually performing as a number. I assume this has something to do with how the querytable is returning my results, but does anyone have an idea for a workaround for this issue?

View 3 Replies View Related

SUM Returning 0 - Calculation Set To Automatic And Cell Formatted As Number

Dec 19, 2013

I have this problem where the SUM function is returning 0. The context is this- I am simulating values using RAND() and looking up the values corersponding to probabilities from a different tables. I then use the TRIM function to return the value I need, and the final number is of a "general" format.

This is the formula in the column I wish to sum:

And this is dragged down the column.

When I change the cells using =VALUE(cell) the SUM function then returns the required value. Oddly the SUMPRODUCT function seems to work.

Calculation is set to automatic and the cell has been formatted as a number.

View 2 Replies View Related

Excel 2010 :: Sumproduct - Calculating Dollar Weighting With One Isolation Factor

Apr 30, 2013

Using excel 2010: I am calculating a dollar weighting with one isolation factor, the state where the sale took place. My current formula is below. Each dollar amount in AG3:AG2000 has a corresponding weighting in AB3:AB2000. The results are isolated by the corresponding state in the range E3:E2000.

I want to add in an additional isolation, the month when the sale took place. The months are located in H3:H2000. Where in my current formula would I insert the isolation for the month?

=SUMPRODUCT(--(Sales!$E$3:$E$2000='2013 NB'!S8),
Sales!$AG$3:$AG$2000,Sales!$AB$3:$AB$2000)/
SUMIF(Sales!$E$3:$E$2000,'2013 NB'!S8,Sales!$AG$3:$AG$2000)

View 9 Replies View Related

Comparing Two Columns And Returning Number Of Cells In One Column With Higher Values?

Apr 8, 2014

I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.

So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.

View 3 Replies View Related

Formula Returning Value N/A

Aug 14, 2013

It works I1 is blank and it works when F7:K7 has data and I1 is initialed. However, when I1 is initaled and there is no data in F7:K7 the cell reads "N/A". It should read "Approved with insufficient data".

=IF(ISBLANK($I$1),"",IF(ISBLANK(F7&G7&H7&I7&J7&K7),"Approved with insufficient data",LOOKUP(99999999999,F7:K7)))

View 4 Replies View Related

Sum Formula Returning Zero Value

Dec 17, 2009

I am trying to sum a column with values using a report downloaded from Celltracker, however the sum total is returning as zero.

When added using =a1+a2 the value returns correctly.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved