Multiple Nested IF Statements

[Nancy18]

Hello All,

I'm having trouble with a large formula in Excel. The spreadsheet has over 2,000 rows and 35 columns. I'm adding two columns (making it 37 columns) for the formulas. I have completed the one column with a multiple nested IF statement, but for whatever the reason, cannot get the other multiple nested IF statement to work.
About the column I need to create the multiple nested IF statement for. It is a column with blanks, and dollar values from a negative number all the way up to $4,000.00. What I'd like to do is break this down to 19 lines (IF statements). When I test the formula for a couple of lines (rows) that formula works, but when I combine more than two IF statements, it's not working. It keeps telling me "You've entered too many arguments for this function." I'm not sure how else to create the formula for this.
Any help would be appreciated! If questions, please let me know.
Thank you in advance!!

Here's the formula I've created:
=IF(ISBLANK(AD3),” “,if(ad3<=0,”Less than $0.00,IF(AD3<=0.25,AD3>=0.01,"$0.01 - $0.25",IF(AD3>=$0.26,ad3<=$0.50,"$0.26 - $0.50",IF(AD3<=0.51,ad3>=0.75,"$0.51 - $0.75",IF(AD3<=0.76,ad3>=1.00,"$0.76 - $1.00",IF(AD3<=1.01,ad3>=2.00,"$1.01 - $2.00",IF(AD3<=2.01,ad3>=3.00,"$2.01 - $3.00",IF(AD3<=3.01,ad3>=4.00,"$3.01 - $4.00",IF(AD3<=$4.01,ad3>=5.00,"$4.01 - $5.00",IF(AD3<=$5.01,ad3>=10.00,"$5.01 - $10.00",IF(AD3<=10.01,ad3>=15.00,"$10.01 – 15.00",IF(AD3<=15.01,ad3>=20.00,"$15.01 – $20.00",IF(AD3<=20.01,ad3>=30.00,"$20.01 – $30.00",IF(AD3<=30.01,ad3>=40.00,"$30.01 – $40.00",IF(AD3<=40.01,ad3>=50.00,"$40.01 – $50.00",IF(AD3<=50.01,ad3>=75.00,"$50.01 – $75.00",IF(AD3<=75.01,ad3>=100.00,"$75.01 – $100.00",IF(ad3>=101.00,"More than $100.01",” “)))))))))))))))))))

 

[Finalfight40]

Re: ** Multiple Nested IF Statements

Do you need AND section here?

IF(AND(AD3<=0.25,AD3>=0.01),"$0.01 - $0.25"

Also follow the same idea for the rest of your formula as well.

Here's the formula I've created:
=IF(ISBLANK(AD3),” “,if(ad3<=0,”Less than $0.00,IF(AD3<=0.25,AD3>=0.01,"$0.01 - $0.25",IF(AD3>=$0.26,ad3<=$0.50,"$0.26 - $0.50",IF(AD3<=0.51,ad3>=0.75,"$0.51 - $0.75",IF(AD3<=0.76,ad3>=1.00,"$0.76 - $1.00",IF(AD3<=1.01,ad3>=2.00,"$1.01 - $2.00",IF(AD3<=2.01,ad3>=3.00,"$2.01 - $3.00",IF(AD3<=3.01,ad3>=4.00,"$3.01 - $4.00",IF(AD3<=$4.01,ad3>=5.00,"$4.01 - $5.00",IF(AD3<=$5.01,ad3>=10.00,"$5.01 - $10.00",IF(AD3<=10.01,ad3>=15.00,"$10.01 – 15.00",IF(AD3<=15.01,ad3>=20.00,"$15.01 – $20.00",IF(AD3<=20.01,ad3>=30.00,"$20.01 – $30.00",IF(AD3<=30.01,ad3>=40.00,"$30.01 – $40.00",IF(AD3<=40.01,ad3>=50.00,"$40.01 – $50.00",IF(AD3<=50.01,ad3>=75.00,"$50.01 – $75.00",IF(AD3<=75.01,ad3>=100.00,"$75.01 – $100.00",IF(ad3>=101.00,"More than $100.01",” “)))))))))))))))))))

 

[Nine Zero]

Re: ** Multiple Nested IF Statements

you are missing AND statements inside each if so for example

=IF(ISBLANK(AD3),” “,if(ad3<=0,”Less than $0.00,IF(AND(AD3<=0.25,AD3>=0.01),"$0.01 - $0.25",IF(AND(AD3>=$0.26,ad3<=$0.50),"$0.26 - $0.50".......and so on

 

[njimack]

Re: ** Multiple Nested IF Statements

Use a lookup table and use either INDEX/MATCH or VLOOKUP to return the desired value.

 

[Nancy18]

Re: ** Multiple Nested IF Statements

Thank you for the responses!!
I've tried the and statement and get the same error message. I don't think the VLOOKUP will work. I need to create a chart out of this data to count each of the lines.

 

[Finalfight40]

Re: ** Multiple Nested IF Statements

Are you also missing some speechmarks here?

”Less than $0.00"

=IF(ISBLANK(AD3),” “,if(ad3<=0,”Less than $0.00,

 

[Joe4]

Re: ** Multiple Nested IF Statements

Also not that Excel does not like slanted speech marks like ” “.
It only wants straight ones like ".

 

[Nine Zero]

Re: ** Multiple Nested IF Statements

found the problem.... apart from the quotes pointed out previously... all of your statements were flipped you had for example if ad3 <=5 and ad3 >=10 print out "5.00 - 10.00" it should be the other way around on the carrots

here it is fixed... tested and works

=IF(ISBLANK(AD3),"",IF(AD3<=0,"Less than $0.00",IF(AND(AD3<=0.25,AD3>=0.01),"$0.01 - $0.25",IF(AND(AD3>=0.26,AD3<=0.5),"$0.26 - $0.50",IF(AND(AD3>=0.51,AD3<=0.75),"$0.51 - $0.75",IF(AND(AD3>=0.76,AD3<=1),"$0.76 - $1.00",IF(AND(AD3>=1.01,AD3<=2),"$1.01 - $2.00",IF(AND(AD3>=2.01,AD3<=3),"$2.01 - $3.00",IF(AND(AD3>=3.01,AD3<=4),"$3.01 - $4.00",IF(AND(AD3>=4.01,AD3<=5),"$4.01 - $5.00",IF(AND(AD3>=5.01,AD3<=10),"$5.01 - $10.00",IF(AND(AD3>=10.01,AD3<=15),"$10.01 – 15.00",IF(AND(AD3>=15.01,AD3<=20),"$15.01 – $20.00",IF(AND(AD3>=20.01,AD3<=30),"$20.01 – $30.00",IF(AND(AD3>=30.01,AD3<=40),"$30.01 – $40.00",IF(AND(AD3>=40.01,AD3<=50),"$40.01 – $50.00",IF(AND(AD3>=50.01,AD3<=75),"$50.01 – $75.00",IF(AND(AD3>=75.01,AD3<=100),"$75.01 – $100.00",IF(AD3>=101,"More than $100.01","")))))))))))))))))))

 

[Nancy18]

Re: ** Multiple Nested IF Statements

Again, thank you all for your help with this!
Nine Zero, thank you for your response and help! This worked!!

Thank you!!