Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't figure out formula error
The following formula works great until I exceed 500,000
units (ie. 480,000 units = $192,00) =D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B21,0) J3 = 540,000 I could use something as simple as the following link but I need it to cover multiple periods and want to show the results on a single spreadsheet: http://cjoint.com/data/ilxdsTVzGk.htm This is how the table is set up on my spreadsheet -exact columns & rows Actual: A B C D Price 19 Tier 1 0 49,999 0.50 20 Tier 2 50,000 499,999 0.40 21 Tier 3 500,000 0.35 Tiers used as baseline to validate formula is correct (Should = $216,000) A B C D Price 19 Tier 1 0 49,999 0.40 20 Tier 2 50,000 499,999 0.40 21 Tier 3 500,000 0.40 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't figure out formula error
I'm thinking the formula should be something like this...
=D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B20-B21,0) Rick "Siper1" wrote in message ... The following formula works great until I exceed 500,000 units (ie. 480,000 units = $192,00) =D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B21,0) J3 = 540,000 I could use something as simple as the following link but I need it to cover multiple periods and want to show the results on a single spreadsheet: http://cjoint.com/data/ilxdsTVzGk.htm This is how the table is set up on my spreadsheet -exact columns & rows Actual: A B C D Price 19 Tier 1 0 49,999 0.50 20 Tier 2 50,000 499,999 0.40 21 Tier 3 500,000 0.35 Tiers used as baseline to validate formula is correct (Should = $216,000) A B C D Price 19 Tier 1 0 49,999 0.40 20 Tier 2 50,000 499,999 0.40 21 Tier 3 500,000 0.40 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't figure out formula error
That worked thanks! For some reason it was $1 off in calculating the higher
tier but that's fine. Thanks again for your patience. It was a great lesson for me to learn. "Rick Rothstein (MVP - VB)" wrote: I'm thinking the formula should be something like this... =D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B20-B21,0) Rick "Siper1" wrote in message ... The following formula works great until I exceed 500,000 units (ie. 480,000 units = $192,00) =D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B21,0) J3 = 540,000 I could use something as simple as the following link but I need it to cover multiple periods and want to show the results on a single spreadsheet: http://cjoint.com/data/ilxdsTVzGk.htm This is how the table is set up on my spreadsheet -exact columns & rows Actual: A B C D Price 19 Tier 1 0 49,999 0.50 20 Tier 2 50,000 499,999 0.40 21 Tier 3 500,000 0.35 Tiers used as baseline to validate formula is correct (Should = $216,000) A B C D Price 19 Tier 1 0 49,999 0.40 20 Tier 2 50,000 499,999 0.40 21 Tier 3 500,000 0.40 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't figure out formula error
It may be fine for you, but it bothers me.<g When I first developed that
formula, the numbers at the top of your tiers end in all zeroes, now they end in all nines. In looking over everything again, I believe the formula should be this instead... =D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B20-C20,0) If you are rounding your values to whole dollars, the above change may bring your calculation in line. Did it? Rick "Siper1" wrote in message ... That worked thanks! For some reason it was $1 off in calculating the higher tier but that's fine. Thanks again for your patience. It was a great lesson for me to learn. "Rick Rothstein (MVP - VB)" wrote: I'm thinking the formula should be something like this... =D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B20-B21,0) Rick "Siper1" wrote in message ... The following formula works great until I exceed 500,000 units (ie. 480,000 units = $192,00) =D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B21,0) J3 = 540,000 I could use something as simple as the following link but I need it to cover multiple periods and want to show the results on a single spreadsheet: http://cjoint.com/data/ilxdsTVzGk.htm This is how the table is set up on my spreadsheet -exact columns & rows Actual: A B C D Price 19 Tier 1 0 49,999 0.50 20 Tier 2 50,000 499,999 0.40 21 Tier 3 500,000 0.35 Tiers used as baseline to validate formula is correct (Should = $216,000) A B C D Price 19 Tier 1 0 49,999 0.40 20 Tier 2 50,000 499,999 0.40 21 Tier 3 500,000 0.40 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error I cannot figure out | Excel Programming | |||
wierd error I cannot figure out | Excel Programming | |||
I can't figure out this formula | Excel Discussion (Misc queries) | |||
error 400, i cant figure this out.. | Excel Programming | |||
Some Error that I can't figure out. | Excel Discussion (Misc queries) |