milage rates for a travel expense form
Hi is it possible to choose a different milage rate in one cell? example: the standard is set at miles x .40p = but if you had an addition passenger it would be miles x .42p ( additional .2p an...
Forum Discussion
Deleted I noticed that the calculation for the rate as given by mtarler does not match your requirement. He usually gives good advice, but we all slip up now and then. While his calculation of the rate is 0.40+IF(B2>1,B2-1,0)/100, my calculation is IF(B2<2, 0.4, IF(B2=2, 0.42, 0.42+(B2-2)*0.01) )
(And yes, the calculation can be phrased accurately in multiple ways.) Plus, in a different design, the formula could use the cells with the number of people to look up the rate in a specific range of cells (or an Excel table) -- probably in a different worksheet -- where the pre-calculated values would stored.
It's good practice to include additional columns/rows to check the intermediate results of your calculations, even if you want to remove those columns/rows (or hide them) later.
And in the attached workbook, I demonstrate that you can use conditional formatting to warn you -- via highlighting -- of problems with the data.
- thx for noticing that error. I believe made the correction above. that is what happen when you do it off the top of your head and don't put it in a sheet and test it.
