Excel formula help

[Gecko]

Any Excel gurus out there?

I am trying to work with formulas 'IF' 'AND'

The conundrum is simply a table that will apply the formula of stamp duty relative to the value of a house being purchased. Example if I enter a value of £245,000 the value should be £245,000 x 1% = £2450

If <= £125000 = 0%
If >= £125001 AND <= £250,000 = 1%
If >= £250,001 AND <= £500,000 = 3%
If >=£500,001 AND <= £1,000,000 = 4%
If >= £1,000,001 AND <= £ 2,000,000 = 5%
If >= £ 2,000,002 = 7%

Anyone know how to make this into a formula that will work in Excel?

 

[Paul Wakefield]

Probably the best way is to set up a table with 0, 125000, 250000, etc in the first column and 0%, 1%, 3%, etc in the second column. Name this range "Rates" or just use the range address.

Then using Vlookup(your number/reference,Rates,2) will retrun the relevant percentage.

Let me know if you need more explanation than this.

 

[GSmonkey]

Not the most elegant solution. B3 is the cell you're looking at:

=IF(B3<=125000,0,IF(AND(B3>=125001,B3<=250000),0.01,IF(AND(B3>=250001,B3<=500000),0.03,IF(AND(B3>=500001,B3<=1000000),0.04,IF(AND(B3>=1000001,B3<=2000000),0.05,0.07)))))

 

[repomanbe]

A simpler formula might be;

if you just want the percentage of stamp duty
=IF(B3>2000000,0.07,IF(B3>1000000,0.05,IF(B3>500000,0.04,IF(B3>250000,0.03,IF(B3>125000,0.01,0)))))

if you just want to find the value of the stamp duty
=IF(B3>2000000,B3*0.07,IF(B3>1000000,B3*0.05,IF(B3>500000,B3*0.04,IF(B3>250000,B3*0.03,IF(B3>125000,B3*0.01,0)))))

if you want to add the stamp duty to the house value in one go
=IF(B3>2000000,(B3+(B3*0.07)),IF(B3>1000000,(B3+(B3*0.05)),IF(B3>500000,(B3+(B3*0.04)),IF(B3>250000,(B3+(B3*0.03)),IF(B3>125000,(B3+(B3*0.01)),B3)))))

 

[Gecko]

Guys , Thanks lots of good stuff - all of which works one way or the other. Now I have choices which I can move forward with !
Beers on me at the hograost!!

 

[Paul Wakefield]

Multilevel nested IF statements with hard coded numbers - spawn of the devil.