Calculating a mileage allowance

[Klarkin]

I'm sure that I'm over complicating.

Essentially I need to calculate the amount per journey where the amount is $0.45 per mile for the first 10,000 and $0.25 thereafter. My formula works fine up to 10,000 and for the journey that crosses the threshold but not for amounts over 10,000

=IF(D6<$I$1,C6*$G$1,IFD6>=$I$1,SUM($I$2-D5)*$G$1+(D6-$I$2)$G2,1))

where G1 = 0.45 and G2 = 0.25 and I1 and I2 equal 10,000
D5 is the previous total number of miles travelled, D6 is the new total number of miles and C6 is the total for the individual journey

Help much appreciated as always!

Kevin

 

[Klarkin]

[TABLE="width: 169"]
<colgroup><col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2016;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2528;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 2688;"> <tbody>[TR]
[TD="width: 63, bgcolor: transparent"]Miles
[/TD]
[TD="width: 79, bgcolor: transparent"]Total miles[/TD]
[TD="width: 84, bgcolor: transparent"]Payable[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[TD="bgcolor: transparent, align: right"]£900.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3000[/TD]
[TD="bgcolor: transparent, align: right"]5000[/TD]
[TD="bgcolor: transparent, align: right"]£1,350.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4000[/TD]
[TD="bgcolor: transparent, align: right"]9000[/TD]
[TD="bgcolor: transparent, align: right"]£1,800.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2000
[/TD]
[TD="bgcolor: transparent, align: right"]11000
[/TD]
[TD="bgcolor: transparent, align: right"]£700.00
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

I'm not quite sure I see what your formula is doing, but if you're trying to calculate mileage for the entire trip, then perhaps this:

=(MIN(C6,$I$1)*$G$1)+(MAX(C6-$I$1,0)*$G$2)

Assuming C6 = total miles and I1 = 10,000

 

[Klarkin]

Thank you dreid1011.

I think I've got closer to what I want with this

=IF(D6<$I$1,C6*$G$1,IF(AND(D6>$I$1,D5<$I$1),($I$1-D5)*$G$1)+((D6-$I$1)*$G$2),C6*$G$2))

But I have a 'too many arguments' error that I can't see.

The first IF statement should be OK where the total miles (D6) is less than 10,000 (I1). The second statement should cover the journey that goes over the 10,000 threshold and the last statement covers all journeys over 10,000.

G1 and G2 are the allowances per mile, C6 is the distance of the individual journey

 

[dreid1011]

Did you try my suggestion? It should handle all situations, and all you need to put in is the total miles. Unless I miss understand how you are trying to calculate it. Anyways, I think I see the issue with your current formula:

=IF(D6<$I$1,C6*$G$1,IF(AND(D6>$I$1,D5<$I$1),(($I$1-D5)*$G$1)+((D6-$I$1)*$G$2),C6*$G$2))

I believe you were missing the "(" highlighted in red.