Help required.

[CelticCharmer]

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.

 

[Mike H]

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]

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]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[CelticCharmer]

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 basis’s (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 basis’s, e.g. Monday L6, Tuesday L7,
Wednesday L8, etc.

 

[Mike H]

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

 

[Mike H]

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


: on sheet 2

 

[CelticCharmer]

I’m 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.

 

[Sandy Mann]

Yes you are of course quite right. I saw MIN and thought MAX.


Sorry for sticking my nose in.

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mike H]

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]

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]

=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