Posted to microsoft.public.excel.misc
|
|
Help required.
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.
|