Nesting of more than 7 in an IF formula

[Rachael F]

Hello

I am using Word 2000.

I have the following formula in cell B1
=IF(A1="Alan",85,IF(A1="Glen",85,IF(A1="Tony",85,IF(A1="Trevor",47,IF(A1="Bob",47,IF(A1="Bobby",47,IF(A1="Andre",47,IF(A1="Clive",47,IF(A1="Claire",47)))))))))

How do I edit the formula to 'combine' Alan, Glen & Tony (equalling '85') in
one part of the formula & all of the other names (equalling 47) in another
part of the formula (to simplify the formula & to avoid a nesting of more
than 7)?

Hope that makes sense.

Many thanks.

Rachael

 

[Niek Otten]

Hi Rachael,

Don't use IF, use VLOOKUP().
Here's a tutorial:

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello
|
| I am using Word 2000.
|
| I have the following formula in cell B1:
|
=IF(A1="Alan",85,IF(A1="Glen",85,IF(A1="Tony",85,IF(A1="Trevor",47,IF(A1="Bob",47,IF(A1="Bobby",47,IF(A1="Andre",47,IF(A1="Clive",47,IF(A1="Claire",47)))))))))
|
| How do I edit the formula to 'combine' Alan, Glen & Tony (equalling '85') in
| one part of the formula & all of the other names (equalling 47) in another
| part of the formula (to simplify the formula & to avoid a nesting of more
| than 7)?
|
| Hope that makes sense.
|
| Many thanks.
|
| Rachael

 

[Dennis]

=IF(OR(A1="Alan",A1="Glen",A1="Tony"),85,47)

 

[ND Pard]

Try this:

Make a 2 column list, it can even be on another worksheet or in another
workbook similar to:

1st Column 2nd Column
Alan 85
Glen 85
Tony 85
Trevor 47
Bob 47
Bobby 47
Andre 47
Clive 47
Alaire 47

Give a range name to the above of: Name_n_No

Then instead of using your If function, replace it with a Vertical Look-Up
function similar to the following example:

=VLOOKUP(A1,'[WorkBookName]SheetName'!Name_n_No,2,FALSE)

This should provide you with the result you desire.

Good Luck.

 

[Pete_UK]

I didn't know that Word supported this type of formula!!

You could try it this way:

=IF(OR(A1="Alan",A1="Glen",A1="Tony"),85,0)+IF(OR(A1="Trevor",A1="B­
ob",A1="Bobby",A1="Andre",A1="Clive",A1="Claire"),47,0)

You could also use a lookup table and then the formula would be
simpler and you could have many more names.

Hope this helps.

Pete

 

[Gaurav]

=IF(OR(A1="Alan",A1="Glen",A1="Tony"),85,IF(OR(A1="Trevor",A1="Bob",A1="Bobby",A1="Andre",A1="Clive",A1="Claire"),47,""))

 

[Ron Coderre]

Assuming that A1 will either contain a valid name or A1 is blank,
here are a couple approaches:

1) All in one formula (in sections for readability)
B1: =IF(A1<>"",VLOOKUP(A1,{"Alan",85;"Glen",85;"Tony",85;"Trevor",47
;"Bob",47;"Bobby",47;"Andre",47;"Clive",47;"Claire",47},2,0),"NA")

2) Using this list of values in D1:E9
Alan 85
Glen 85
Tony 85
Trevor 47
Bob 47
Bobby 47
Andre 47
Clive 47
Claire 47

B1: =IF(A1<>"",VLOOKUP(A1,D1:E9,2,0),"NA")

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Rachael F]

Thanks very much for all the replies. I have used Pete's formula.

Best wishes.

Rachael

 

[Pete_UK]

Thanks for feeding back, Rachael.

Note that my formula will return a zero if A1 does not contain one of
the listed names. Also, the IFs themselves are not nested, so you do
not suffer from the limit.

Pete