Nesting IF(AND statements

June 24, 2009 at 15:40:22
Specs: Windows XP
I'd like to write a formula in EXCEL 2007 that allows me to determine if the number is within a certain range and then give it a label. I have membership levels that are determined by money amounts. The levels are:

Individual =$35
Family =$50
Contributor= $100-299

Here's the formula I tried to write, but it's giving me an error.

=IF(H3=35,"Individual"),IF(H3=50,"Family"), IF(AND(H3>99,H3<300),"Contributor"), IF(AND(H3>299, H3<500),"Guardian"), IF(AND(H3>499, H3<1000),"Aficionado")

See More: Nesting IF(AND statements

Report •

June 24, 2009 at 15:59:52
I figured it out!

=IF(H61=35,"Individual",IF(H61=50,"Family",IF(AND(H61>99,H61<300),"Contributor",IF(AND(H61>299,H61<500),"Guardian", IF(AND(H61>499,H61<1000),"Aficionado",IF(AND(H61>999,H61<2500),"Director's Circle"))))))


Report •

June 24, 2009 at 16:47:37
It's great that you kept at it and figured it out. Also, thanks for letting us know so we didn't spend time working on it.

I'd like to offer an alternative, just to show you something that might come in handy later.

You have 6 Nested IF's. The limit is 7, so you are OK. However, here is a way to eliminate 1 IF, so if you ever run into the 7 limit, you can gain one more. Again, there is nothing wrong with your solution, I'm just offering a "technique".

Since you have room for a final value_if_false, I moved the "Individual" answer to the end. The assumption is that if none of the other criteria are met, then the only membership level left must be "Individual". In other words, there is no need to check for a value of 35, so we eliminate an IF clause.

I also assume that the upper limit of 2500 isn't really necessary since your formula would return FALSE for values above 2499. You didn't specify a level above "Director's Circle" so I assume that's the highest. That eliminated an AND at the end also.

IF(H61>999,"Director's Circle","Individual")))))

Report •

Related Solutions

Ask Question