Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel formula
I need to know how to put in a formula that will let me calculate something
like this. 0 to 5 miles $0 next 25 miles is $2.15 each next 30 miles is 2.25 each next 40 miles is 2.10 each for a total of 100 miles so if i entered 100 miles it would calculate the first 5 at 0$ then the next 25 at $2.15 then the next 30 at $2.25 then the next 40 at $2.10. for a total of 100 miles and display the correct dollar amount. but if i entered 65 miles it would calculate the first 5 at 0$ then the next 25 at 2.15 then the next 30 at 2.25 then the next 5 at 2.10 for a total of 65 miles and give the correct dollar amount. Thanks Clint. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel formula
Hi,
I don't think you tell is what happens after 100 miles. To get a figure for miles in excess of 100 miles you will need yo adjust the last Number 0.4. The logic is simple enough , the 0.4 represents the difference to the previous figure for 61 - 100 miles (2.1) so the final figure of 0.4 would award 2.5 for all miles in excess of 100. =SUMPRODUCT(--(A1{0;5;30;60;100}),(A1-{0;5;30;60;100}), {0;2.15;0.1;-0.15;0.4}) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Clinton Spry" wrote: I need to know how to put in a formula that will let me calculate something like this. 0 to 5 miles $0 next 25 miles is $2.15 each next 30 miles is 2.25 each next 40 miles is 2.10 each for a total of 100 miles so if i entered 100 miles it would calculate the first 5 at 0$ then the next 25 at $2.15 then the next 30 at $2.25 then the next 40 at $2.10. for a total of 100 miles and display the correct dollar amount. but if i entered 65 miles it would calculate the first 5 at 0$ then the next 25 at 2.15 then the next 30 at 2.25 then the next 5 at 2.10 for a total of 65 miles and give the correct dollar amount. Thanks Clint. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel formula
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel formula
hI,.
=SUMPRODUCT(--(A1{0;5;30;60}),A1-{0;5;30;60},{0;2.15;0.1;-0.15}) "Clinton Spry" wrote: I need to know how to put in a formula that will let me calculate something like this. 0 to 5 miles $0 next 25 miles is $2.15 each next 30 miles is 2.25 each next 40 miles is 2.10 each for a total of 100 miles so if i entered 100 miles it would calculate the first 5 at 0$ then the next 25 at $2.15 then the next 30 at $2.25 then the next 40 at $2.10. for a total of 100 miles and display the correct dollar amount. but if i entered 65 miles it would calculate the first 5 at 0$ then the next 25 at 2.15 then the next 30 at 2.25 then the next 5 at 2.10 for a total of 65 miles and give the correct dollar amount. Thanks Clint. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I enter a formula, Excel shows the formula not the results | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) |