Hi,

Alll the formula you have been given work for example 148 miles in L5 of
sheet 2

=MIN(Sheet2!L5*0.48,48) returns £48.00 for the first 100 miles
=MAX((Sheet2!L5-100)*0.25,0) returns £12.00 for the 48 miles over 100 miles

What is it you want? the sum of L5 L6 etc?


L5 100 miles
L6 40 Miles
L7 999 Miles

What result would you expect from this data?

Mike


"CelticCharmer" wrote: on sheet 2

Hi and again I am very thankful for the replies,
I have just both formulas and they are not working. Maybe is it me€¦
I only want to input my daily mileage into sheet 2, L5, L6, L7 etc on a
daily basiss (could be 198, 45, 150, 95, etc) but I want excel to break it
up for me into cells B18 for the 0.40 rate(for the first 100) and B19 For the
0.25 rate(if over 100). Sheet 2 cells L6, L7, L8 etc will also be put into
B18 & B19 as I fill them in on a daily basiss, e.g. Monday L6, Tuesday L7,
Wednesday L8, etc.


"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.