MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### excel formula

• Follow

```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.
```
 0

```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.
```
 0

```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
```
 0

```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.
```
 0

3 Replies
179 Views

Similiar Articles:

7/23/2012 7:35:20 PM