If function with more than 7 conditions

[Guest]

Hi!

Hope you can help me make if function that can compute for 13 conditions.

The data I'm working on is as follows:

A B C D E F G H I J K L M
1 1
2 1
3 1
4 1
5 1
6 1

I need a summary which says:
If Column A = 1, XS
If Column B = 1, S
If Column C = 1, M
If Column D = 1, L
If Column E = 1, XL
and so on...

A website I checked suggest VBA functions, but I really don't know how to
work with them.

Hope you can help me out.

Super thanks!

 

[T. Valko]

Need more info. It's not clear what you want.

In your sample it looks like cell A1 = 1 and.....

If Column A = 1, XS

OK. So, what do you want to do with that?

Biff

 

[macropod]

A lookup table is probably the best way for dealing with this, but you can
still do it with IF functions if you really want to. Consider:
=IF(A1=1,"XS","")&IF(B1=1,"S","")&IF(C1=1,"M","")&IF(D1=1,"L","")&IF(E1=1,"XL"
,"")&IF(F1=1,"XXL","")
With this structure, there is no nesting of the IF functions, so the 7-nested
levels limit doesn't apply (not that 7 levels would be needed for the posted
example anyway):
=IF(A1=1,"XS",IF(B1=1,"S",IF(C1=1,"M",IF(D1=1,"L",IF(E1=1,"XL",IF(F1=1,"XXL","
"))))))
You could also nest one set like this, then have another following it (with a
different set of conditions, like:
=IF(A1=1,"XS",IF(B1=1,"S",IF(C1=1,"M",IF(D1=1,"L",IF(E1=1,"XL",IF(F1=1,"XXL","
"))))))&IF(G1=1,"XS",IF(H1=1,"S",IF(I1=1,"M",IF(J1=1,"L",IF(K1=1,"XL",IF(L1=1,
"XXL",""))))))

Cheers

--
macropod
[MVP - Microsoft Word]


| Hi!
|
| Hope you can help me make if function that can compute for 13 conditions.
|
| The data I'm working on is as follows:
|
| A B C D E F G H I J K L M
| 1 1
| 2 1
| 3 1
| 4 1
| 5 1
| 6 1
|
| I need a summary which says:
| If Column A = 1, XS
| If Column B = 1, S
| If Column C = 1, M
| If Column D = 1, L
| If Column E = 1, XL
| and so on...
|
| A website I checked suggest VBA functions, but I really don't know how to
| work with them.
|
| Hope you can help me out.
|
| Super thanks!
|

 

[Roger Govier]

Hi

You could use something like the following
=IF(COUNT(A1:J1)=0,"",
CHOOSE(MATCH(1,A1:J1),"XS","S","M","L","XL","2XL","3XL","4XL","5XL","Gigantic"))

This would give you up to 10 sizes ( I couldn't think what to enter
after this!!)
If a mistake is made and there 2 entries on 1 in any row, it will always
display the result for the larger of the 2 entries.

Alter the number of columns (A1:M1 to give 13 choices) and sizes to
suit.