If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
excel formula
|
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|