Apr 03 2019 03:58 AM
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!!
Apr 03 2019 04:23 AM
Apr 03 2019 04:39 AM
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)
Apr 03 2019 05:39 AM
Apr 03 2019 06:25 AM
@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)
Apr 03 2019 08:02 AM
Apr 03 2019 08:42 AM
@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
Apr 03 2019 10:03 AM
Apr 03 2019 10:29 AM
@Twifoo , depends on needs, actually it is within the formula as
=SUMIF(INDEX([Description],1):[@Description],"Mileage",INDEX([Distance],1):[@Distance])