Calculating Mileage

Copper Contributor

Hi I am creating a template for expenses, when calculating Mileage for the first 10000 miles it is billed at £0.45 anything over 10000 is £0.25. I cant seem to figure out a formula that will work for this. 

I need to calculate mileage at £0.45 for the first 10000 miles and after that I need to calculate any additional miles at £0.25. Where I have got to for now:

=IF(AND([@Description]="Mileage",L9<10000),[@Distance]*Table26[Mileage Allowance (<10000)],[@Distance]*Table26[Mileage Allowance (>10000)])

The difficulty I am having is stopping the previously calculated figures from updating with the new rate.

Any help would be much appreciated!!

8 Replies
Does this fit your needs:
=IF([@Description]="Mileage",MIN([@Distance],10000)*Table26[Mileage Allowance (<10000)]+MAX(0,[@Distance]-10000)*Table26[Mileage Allowance (>10000)],0)

Not quite, for example if I enter 10050 I need it to calculate the 10000 @0.45 and then the 50 @0.25, then each subsequent entry after this would need to calculate at 0.25 as it is past the threshold of 10k 


@Jan Karel Pieterse wrote:
Does this fit your needs:
=IF([@Description]="Mileage",MIN([@Distance],10000)*Table26[Mileage Allowance (<10000)]+MAX(0,[@Distance]-10000)*Table26[Mileage Allowance (>10000)],0)


 @Jan Karel Pieterse 

Without an idea of your data this is hard to answer. Are you saying this must not be calculated on a per-row basis but that we need to keep a running total of mileage?

@Conor5283 , if it us assumed cumulative mileage, when for that sample

image.png

that could be

=IF([@Description]="Mileage",
  MIN(SUMIF(INDEX([Description],1):[@Description],"Mileage",INDEX([Distance],1):[@Distance]),10000)*$G$4+
  MAX(0,SUMIF(INDEX([Description],1):[@Description],"Mileage",INDEX([Distance],1):[@Distance])-10000)*$G$5,0)
Perhaps, this is the formula you need:
=LOOKUP([@Distance],
{0,10000},
{0,4500}+([@Distance]-{0,10000})*{0.45,0.25})

@Twifoo , I guess it returns the same as @Jan Karel Pieterse formula, as I understood the question is in

 then each subsequent entry after this would need to calculate at 0.25 as it is past the threshold of 10k 

 

If that is so, it would be easier if a Cumulative Distance column is added to the table.

@Twifoo , depends on needs, actually it is within the formula as

=SUMIF(INDEX([Description],1):[@Description],"Mileage",INDEX([Distance],1):[@Distance])