Formula needed

[dancingbear72]

Right all you clever people, I need a formula for my tax spreadsheet
who can help me?

I'm doing my mileage. I can claim back 40p a mile for the first 4,00
miles, then 25p a mile after that.

I've been using this formula for the 40p a mile: =D75*0.4 (D75 bein
the "total miles" cell)

Fairly straightforward, but how do I make it so it adds 0.4 for th
first 4,000 then 0.25 for everything after that. Is this possible?

Thanks in advance

 

[ElsiePOA]

Try this:

=IF(D75<=4000,D75*0.4,1600+(D75-4000)*0.25)

:cool

 

[Jazzer]

Hi,

Or simply try this:

=MIN(D75,4000)*0.4+MAX(D75-4000,0)*0.25

- Asse

 

[Alex]

Try this formula. I assume your total mileage is in
column A1 and then down e.g. A1, A2, A3 etc..

Example
A B
1 6000

In cell B1 type...

=IF(A1<=4000,A1*0.4,IF(A1>4000,((4000*0.4)+(A1-4000)
*0.25),""))

This should work.

So if a vlaue is 4000 miles or less then it is just
multiplied by 0.4.
If a value is greater than 4000 e.g. 6000, then the first
4000 is * 0.4 and then the remainder i.e. 6000-4000=2000
is multiplied by 0.25.

You can drag this formula down the Excel sheet.

 

[Dana DeLouis]

Same as others, just slightly different:

=MIN(D75*0.4, 600 + D75/4)

HTH
Dana DeLouis