Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this type of formula possible in Excel?
I am trying to apply a multiplication factor that would vary depending on the
sum of a particular cell. For example let's say the value of cell A5 is the SUM of A1:A4. Let's say the value is 255,000,000. my multiplication factor table looks like this: 1 - 50,000,000.........................(0.17)/1000 50,000,001 - 100,000,000..........(0.13)/1000 100,000,001 - 250,000,000.........(0.10)/1000 250,000,001 - 500,000,000.........(0.08)/1000 500,000,001 - 750,000,000.........(0.07)/1000 750,000,000 ++ .......................(0.05)/1000 So depending on the range the cell sum falls into I need the formula to apply the appropriate multiplication factor and divide by 1000. I'm trying to avoid manually inputting the (0.XX)/1000 based on the formula identifying which range the sum falls into and automatically applying the appropriate multiplication factor. In the example above the formula would be applied to the A6 cell and identify the A5 sum as falling into the "range" and automatically apply the corresponding multiplication factor. Is something like this more advanced than what Excel can handle? Thanks in advance for any perspective on this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this type of formula possible in Excel?
KP wrote...
.... 1 - 50,000,000.........................(0.17)/1000 50,000,001 - 100,000,000..........(0.13)/1000 100,000,001 - 250,000,000.........(0.10)/1000 250,000,001 - 500,000,000.........(0.08)/1000 500,000,001 - 750,000,000.........(0.07)/1000 750,000,000 ++ .......................(0.05)/1000 So depending on the range the cell sum falls into I need the formula to apply the appropriate multiplication factor and divide by 1000. . . . .... =A5*LOOKUP(A5,{0;50;100;250;500;750}*1000000,{.17; .13;.1;.08;.07;.05}/ 1000) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this type of formula possible in Excel?
Enter this in a blank Col, say Col M,
1 50,000,001 100,000,001 250,000,001 500,000,001 750,000,000 then enter this in Col N 0.17 0.13 0.1 0.08 0.07 0.05 then assuming you have your values in Col A enter this in B1 and copy down till end of your data set =VLOOKUP(A1,M:N,2,TRUE) This will give you the factor for the value in A1 You can also enter =VLOOKUP(A1,M:N,2,TRUE)*A1/1000 to get the final result you want Note the formula will fail if the cell in Col A contains a zero or non-numeric value. -- Always provide your feedback... "KP" wrote: I am trying to apply a multiplication factor that would vary depending on the sum of a particular cell. For example let's say the value of cell A5 is the SUM of A1:A4. Let's say the value is 255,000,000. my multiplication factor table looks like this: 1 - 50,000,000.........................(0.17)/1000 50,000,001 - 100,000,000..........(0.13)/1000 100,000,001 - 250,000,000.........(0.10)/1000 250,000,001 - 500,000,000.........(0.08)/1000 500,000,001 - 750,000,000.........(0.07)/1000 750,000,000 ++ .......................(0.05)/1000 So depending on the range the cell sum falls into I need the formula to apply the appropriate multiplication factor and divide by 1000. I'm trying to avoid manually inputting the (0.XX)/1000 based on the formula identifying which range the sum falls into and automatically applying the appropriate multiplication factor. In the example above the formula would be applied to the A6 cell and identify the A5 sum as falling into the "range" and automatically apply the corresponding multiplication factor. Is something like this more advanced than what Excel can handle? Thanks in advance for any perspective on this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this type of formula possible in Excel?
Hi,
And a combination of the previous ideas with some modifications: In a range, say G1:H6 enter the table: 1 0.00017 50,000,001 0.00013 100,000,001 0.0001 250,000,001 0.00008 500,000,001 0.00007 750,000,000 0.00005 In another cell enter the formula: =A5*LOOKUP(A5,G1:G6,H1:H6) -- Thanks, Shane Devenshire "KP" wrote: I am trying to apply a multiplication factor that would vary depending on the sum of a particular cell. For example let's say the value of cell A5 is the SUM of A1:A4. Let's say the value is 255,000,000. my multiplication factor table looks like this: 1 - 50,000,000.........................(0.17)/1000 50,000,001 - 100,000,000..........(0.13)/1000 100,000,001 - 250,000,000.........(0.10)/1000 250,000,001 - 500,000,000.........(0.08)/1000 500,000,001 - 750,000,000.........(0.07)/1000 750,000,000 ++ .......................(0.05)/1000 So depending on the range the cell sum falls into I need the formula to apply the appropriate multiplication factor and divide by 1000. I'm trying to avoid manually inputting the (0.XX)/1000 based on the formula identifying which range the sum falls into and automatically applying the appropriate multiplication factor. In the example above the formula would be applied to the A6 cell and identify the A5 sum as falling into the "range" and automatically apply the corresponding multiplication factor. Is something like this more advanced than what Excel can handle? Thanks in advance for any perspective on this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a certain type of Excel formula? | Excel Worksheet Functions | |||
HOW DO YOU TYPE - OR + IN EXCEL WITHOUT CREATING A FORMULA? | Excel Discussion (Misc queries) | |||
Excel will not let me type any characters....I can type in Word. | Excel Discussion (Misc queries) | |||
Excel will not let me type any characters....I can type in Word. | Excel Discussion (Misc queries) | |||
How to I use ** without Excel thinking I want to type a formula? | Excel Discussion (Misc queries) |