Excel change formula after a certain amount

[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

 

[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

 

[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

 

[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)

 

[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