Hi,

In which case put these 2 into B18 & B19 on sheet 1

=MIN(Sheet2!L5*0.48,48)

=MAX((Sheet2!A1-100)*0.25,0)

Mike

"CelticCharmer" wrote:

Hi Mike, thanks for the quick reply.
I'm putting 148miles onto sheet 2, L5, L6,(one per day) etc, and I want it
to automatically put up to 100 onto sheet 1, B18 and anything above 100 as in
48 onto sheet 1, B19. Now thats when I go over 100, some days I do less. I
have the formula ready to sum up the values of B18 and B19.I hope that clears
it up a little.

"Mike H" wrote:

Hi,

You don't say which cells were using so this assumes the mileage is entered
in a1

Use this for first 100 miles
=MIN(A1*0.48,48)

and for any miles in excess of 100
=MAX((A1-100)*0.25,0)

Format both these cells with your currency

Mike

"CelticCharmer" wrote:

Hiya all,
I am hope someone can help me out. I am trying to redo my travel expense
sheet on to excel to save time. So here is my problem, if I travel 148 miles
in one day the first 100miles are at a rate of £0.40p/m and anything above
100miles (48miles) is at a rate of £0.25
I want to input the 148miles into one cell and use a formula to
automatically fill in the two required cells, is this possible? (as I am not
allowed to change the layout of the expense sheet) I have the formulas done
for each rate. I just need to put the right mileage into the different cells.
I have the expense sheet on two sheets on excel because I cant turn one
page into landscape while keeping sheet 1 as portrait. Is there another way?
Or can I copy excel to word keeping the formulas? To make it easier to print
when finished?
Thank you for the time reading this and I hope you can help. Please take it
easy on me as I am not use to excel.
Celtic Charmer.