Forum Discussion

Deleted's avatar
Deleted
Nov 29, 2022

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

any more passengers would be an addition 1p per mile (3 people =43.) (4 people =44p)

 

is this possible to hover over a cell and choose the additional rate?

 

 

Formulas and Functions
  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    Nov 29, 2022

    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.

     

    • mtarler's avatar
      mtarler
      Silver Contributor
      Nov 29, 2022
      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.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 29, 2022

    You can't "hover over a cell and choose" per se but you can have a column called rate or # of people and then the calculation in the total cell can take that into account.
    For example you have:
    miles . . . . . # people . . . . . $$
    195 . . . . . . . . . 3 . . . . . . . . . = A2 * (0.40+IF(B2>1,B2,0)/100)
    and that formula will add 2,3,... cents to the 0.40 if there are more than 1 people in the car and then multiple by the miles for the total rate.

     

    EDIT: as noted by SnowMan55 I removed the -1 inside the IF statement. (I have no idea why I put that there because I knew it shouldn't be.)

Resources