Excel change formula after a certain amount


C

Colin Weir

Hi there

I would like to have a formula in a cell that calculates something
where it is up to a certain value and when it's over that to change
the formula.

It is for my millage sheet at work. We claim back 40p on each mile we
do for work up to 80 miles - thereafter it becomes 20p per mile. So
the first 80 miles is always at 40p. Currently I put in the full
amount of miles and then put a negative amount in my "additional
expenses" column to make the change.

Formula at the moment is simple - =D6*0.4+E6

Is there any way of doing this? Had a go but didn't get anywhere!

Thanks

Colin
 
Ad

Advertisements

J

Jim Cone

=(D6*0.2)+MIN(80,D6)*0.2

--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Excel programs: some free, some for sale)

..
..
..

"Colin Weir" <[email protected]>
wrote in message
Hi there

I would like to have a formula in a cell that calculates something
where it is up to a certain value and when it's over that to change
the formula.

It is for my millage sheet at work. We claim back 40p on each mile we
do for work up to 80 miles - thereafter it becomes 20p per mile. So
the first 80 miles is always at 40p. Currently I put in the full
amount of miles and then put a negative amount in my "additional
expenses" column to make the change.

Formula at the moment is simple - =D6*0.4+E6

Is there any way of doing this? Had a go but didn't get anywhere!

Thanks

Colin
 
C

Colin Weir

Hi

Thanks for that - really appreciate it and works fine. Only problem
is that when the cell (D6 in this case) is empty it outputs a value of
£16.00. Which is 80*0.2.

Thanks

Colin
 
R

Rick Rothstein

You can use the method of testing the cell first to see if it is empty
before applying the formula...

=IF(D6="","",(D6*0.2)+MIN(80,D6)*0.2)
 
Ad

Advertisements

C

Colin Weir

Hi There

Thanks for everyones help - got it from the suggestions given.

IF(D6="","",(D6*0.2)+MIN(80,D6)*0.2

Regards

Colin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top