Forum Discussion
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...
Perhaps, this is the formula you need:
=LOOKUP([@Distance],
{0,10000},
{0,4500}+([@Distance]-{0,10000})*{0.45,0.25})
=LOOKUP([@Distance],
{0,10000},
{0,4500}+([@Distance]-{0,10000})*{0.45,0.25})
Twifoo , I guess it returns the same as JKPieterse 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])