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,I F(A1="Trevor",47,IF(A1="Bob",47,IF(A1="Bobby",47,I F(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

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

"Rachael F" wrote in message ...
| 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,I F(A1="Trevor",47,IF(A1="Bob",47,IF(A1="Bobby",47,I F(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

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

"Rachael F" wrote:

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,I F(A1="Trevor",47,IF(A1="Bob",47,IF(A1="Bobby",47,I F(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

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.
"Rachael F" wrote:

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,I F(A1="Trevor",47,IF(A1="Bob",47,IF(A1="Bobby",47,I F(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

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

On May 15, 4:12*pm, Rachael F
wrote:
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,I F(A1="Trevor",47,IF(A1="B*ob",47,IF(A1="Bobby",47, IF(A1="Andre",47,IF(A1="Clive",47,IF(A1="Claire",4 7*)))))))))

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

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


"Rachael F" wrote in message
...
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,I F(A1="Trevor",47,IF(A1="Bob",47,IF(A1="Bobby",47,I F(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

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" wrote in message
...
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,I F(A1="Trevor",47,IF(A1="Bob",47,IF(A1="Bobby",47,I F(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

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

Best wishes.

Rachael

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

On May 16, 9:33*am, Rachael F
wrote:
Thanks very much for all the replies. I have used Pete's formula.

Best wishes.

Rachael