excel formula

[Clinton Spry]

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.

 

[Mike H]

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.

 

[Ron Rosenfeld]

On Mon, 5 Apr 2010 03:14:01 -0700, Clinton Spry <Clinton

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.

One method is to set up a Table where each row is set to the "Change Point" and
the columns are the amount up to that point. Then use a VLOOKUP formula.

For example, set up a Table as follows:

0 $0.00 $0.00
5 $0.00 $2.15
30 $53.75 $2.25
60 $121.25 $2.10

Column 2 is the total amount for the value in column 1. So if your Table was
in G1:I4, then

H1: 0
H2: =(G2-G1)*I1+H1
and fill down to H4.

Then use this formula, with your entry in A1, and Tbl referring to your Table:

=(A1-VLOOKUP(A1,Tbl,1))*VLOOKUP(A1,Tbl,3)+VLOOKUP(A1,Tbl,2)

This has an advantage of being rather easily maintainable so far as any
changes, or extension you wish to make to the table.
--ron

 

[Eduardo]

hI,.

=SUMPRODUCT(--(A1>{0;5;30;60}),A1-{0;5;30;60},{0;2.15;0.1;-0.15})