Formula needed

D

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
 
A

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.
 
D

Dana DeLouis

Same as others, just slightly different:

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

HTH
Dana DeLouis
 

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

Similar Threads

Formula Help 0
Excel change formula after a certain amount 4
Formula Help! 8
> or < Than 2
Conditional formula in Excel 3
formula question 1
Excel Formula Challenge... 9
Formula question 5

Top