Hi Mike,
The simple way it this, forget everything else.
I want is to type in the mileage into sheet 2, L5 say 148 and I want excel
to automatically put 100 and less to sheet 1, B18 anything over 100 (48)
into sheet 1, B19. I need to leave the 148 in L5 sheet 2.


"Mike H" wrote:

hi,

Lets try again.

this sums L5:L25 on sheet 2 and splits it up

For the first 100
=MIN(SUM(Sheet2!L5:L25)*0.48,48)
For anythin over 100
=MAX((SUM(Sheet2!L5:L25)-100)*0.25,0)

Change L25 to the last cell you want

Mike

"CelticCharmer" wrote:

Im sorry about the confusion as I am not explaining myself correctly because
I am not use to excel.
I want is to type in the mileage into L5, L6, L7 etc say €œ148, 50, 75 250
etc€�, so L5=148, L6=50, L7=75 L8=250 etc.
I want excel to automatically put 100 and less to go to sheet 1 into B18
anything over 100 into sheet 1, B19.
I can work out what to do from there. You have given me that information.
Thank you very much for your time and effort. I do appreciate it a lot.


"Mike H" wrote:

If the OP doesn't do 100 miles then my formula

=MIN(A1*0.48,48)

returns precisely the same as yours

=MIN(100,A1)*0.48

Mike

"Sandy Mann" wrote:

Hi Mike,

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

Supposing that the OP does not do 100 miles?

May I suggest:

=MIN(100,A1)*0.48

To the OP, if you want blank looking cells until an entry is made in A1 then
try wrapping the formulas in an IF() like:

=IF(A1="","",MIN(100,A1)*0.48)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Mike H" wrote in message
...
Hi,=IF(MAX(INDEX(ABS(A1:A3),))<=1,"A comment","B Comment")


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 can't 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.