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.

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.

On Mon, 5 Apr 2010 03:14:01 -0700, Clinton Spry <Clinton
Spry@discussions.microsoft.com> 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.

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

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.