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

Guardian=$300-499

etc.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")

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"))))))

:-)

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=50,"Family",

IF(AND(H61>99,H61<300),"Contributor",

IF(AND(H61>299,H61<500),"Guardian",

IF(AND(H61>499,H61<1000),"Aficionado",

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

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History