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.
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?
Which formula should I use to look for the present value factor if I have the interest rate and the # of periods. attached file.
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 RelatedCode:
=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.
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 RelatedI 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.
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 RelatedI 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.
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 RelatedSee 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
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?
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 RelatedI 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 RelatedI 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
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.
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.
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
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?
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).
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.
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 RelatedI 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 RelatedI 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.
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 RelatedI 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.
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)
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.
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)))
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.