• 469K Members
• 5,119 Online
• 568K Conversations

New Contributor

# Calculating Mileage

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

# Re: Calculating Mileage

=IF([@Description]="Mileage",MIN([@Distance],10000)*Table26[Mileage Allowance (<10000)]+MAX(0,[@Distance]-10000)*Table26[Mileage Allowance (>10000)],0)

# Re: Calculating Mileage

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:
=IF([@Description]="Mileage",MIN([@Distance],10000)*Table26[Mileage Allowance (<10000)]+MAX(0,[@Distance]-10000)*Table26[Mileage Allowance (>10000)],0)

# Re: Calculating Mileage

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?

# Re: Calculating Mileage

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

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

# Re: Calculating Mileage

Perhaps, this is the formula you need:
=LOOKUP([@Distance],
{0,10000},
{0,4500}+([@Distance]-{0,10000})*{0.45,0.25})

# Re: Calculating Mileage

@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

# Re: Calculating Mileage

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

# Re: Calculating Mileage

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

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies