Thread: 'When' function

I'm attempting to put tgether a mileage expense sheet, which will include a list of rows into which the employee will input their mileage for each business related journey.

Mileage will be reimbursed @ £0.40 per mile to 10k miles, thereafter @ £0.25 per mile.

Reimbursments need to be shown for each row, to correctly calculate mileage @ 25p when 10k miles is reached, but I can't establish the correct formula. Anyone's assistance would be much appreciated. Many thanks.

Thanks so much, however I have tried using the formula, but if mileage exceeds 10k, the formula considers all mileage below 10k @ £0.25, rather than £0.40. In effect £4,000 can be claimed before £0.25 mileage rate is applied. Does this clarify? Any further assistance would really help. Many thanks

Hello Suzy,

Starguy's formula works OK for me....although I think you could use a shorter formula, i.e.

=A1*0.25+MIN(A1,10000)*(0.4-0.25)

If that doesn't work for you can you give an example where it doesn't, i.e. data, result returned by formula, required result

My sincerest thanks to you both. Both formulas work perfectly. Best regards.