Excel if statement error?

Question

im doing quite a long if statement for my ict as level and i cannot get it to work (excel if statements aren't my strong point) can you have a look at the if statement to see where ive gone wrong and correct it please (the teacher can tell us its wrong but not how to correct it) i either get "you've entered to many arguments for this function" or "the formula contains an error"

we have to make excel calculate the stamp duty on properties which is a percentage of the property type

Stamp Duty. This is calculated as a percentage of the Property Price. First time buyers who

purchase a property priced at £250,000 or less will not be charged stamp duty. The interface

should contain a check box that can be ticked if the purchaser is a first time buyer.

and the stamp duty is as follows:

£0 to £125,000 0%

£125,001 to £250,000

£250,001 to £500,000 3%

£500,001 to £1,000,000 4%

£1,000,001 and above 5%

=IF(B17=TRUE,B15<=250000,0,)IF(B17=FALSE,B15<=125000,0,IF(B15=>125001,B15<=250000,*0.01,IF(B15=>250001,B15<=500000,*0.03,IF(B15=>500001,B15<=1000000,*0.04,IF(B15=>1000001,*0.05)))))

thanks in advance for any help ;)

Update:

this is practice but i still cant do it :/

mike_raver_1999 · Accepted Answer

We can't see the full IF statement in your question, and I don't know what stamp duty you want for properties between £125k and £250k (in my example below I've used 2%).  I would do it using TWO cells.

FORMULA FOR CELL 1 - PLACE IN CELL C20
=if(b15>=1000001,(b15*0.05),if(b15>=500001,(b15*0.04),if(b15>=250001,(b15*0.03),if(b15>=125001,(b15*0.02),0))))

FORMULA FOR CELL 2 - PLACE IN CELL C21
=if(b17=TRUE,if(b15<=250000,0,C21),C21)

And Cell C21 should have your answer in it.

I'm assuming B17 is the tickbox, and B15 is the property value.

The trick with IF statements is that they really aren't good at measuring more than one value at a time (e.g. asking them if B17 is true AND B15 is less than £250,001) so it's how you get round that.

Don't know if the above is any good, but it should work.

WK of Angmar · Answer

You should not use IF statements for the entirety of this. You should use an IF statement to check for a first time buyer. If that is true, then give the value as 0. Else, you need to use the VLOOPKUP function: https://office.microsoft.com/en-gb/excel-help/vlookup-HP005209335.aspx
It checks a table for the value that you have specified, then gives the value of another column in the same row.

Here is a partial solution (I have not included a checkbox macro as I assume that you know how to use it): http://www.mediafire.com/download/b6csqat6f3j5824/Sam.xlsx
Make sure to download and save (not view) to see the formulae.

Chris D · Answer

Responding to mike_raver_1999's comment about IF statements not being able to handle multiple conditions...

The keyword is AND:
IF ( AND (condition1, condition2, ...etc...), true_value, false_value )

There are also OR () and NOT ().

But actually I wouldn't solve the original Question with a massive complex nested IF statement unless it really was "throwaway code". Instead, I'd take the VLOOKUP approach as recommended by WK of Angmar.

Trending News

Excel if statement error?

3 Answers