Excel Tips - Help Wanted!

[Goddess of Whatever]

Due to my staffs always dispatch a wrong software again and again then I want to help them to remind themselve that the program that they are using is wrong. I will ask them to record the models and software version that they use to test in each model in exel then I have an idea that if they put a wrong s/w version then it will show "Fault".

Anyway, I just put this formula in excel, it works for fixing that cell with only one s/w version but I want to use many formulas in one cell, can I do that?

=IF(A1="A",B1="A.B.C","Fault")
+
IF(A1="B",B1="A.B.D","Fault")
+
IF(A1="C",B1="A.B.E","Fault")

What can I do to merge these formulas together? Or is there any other formulas that fit with what I want?

This is a sample.

Model S/W version Status
A A.B.C True
B A.B.D True
C A.B.E True

Help me please..

GoW

[lom]

I think you are looking for the 'logical or' operation GoW.
Don't have Excel installed at the moment so can't help you but look in
Excel help index.

[Dougal]

I'm just going home and need to catch a train but :- if you only have a few options you can nest the IF statemnts up to 7 deep

if(a1="A",if(a1="B",if(a1="C" ......)))

but that gets pretty messy fairly quickly.

I think the LOOKUP statement would fit the bill but I'll have to (ha) look up at home.

[lom]

Try this:

=IF(AND(A1="A",B1="A.B.C","Fault")),IF(AND(A1="B", B1="A.B.D","Fault")),IF(AND(A1="C",B1="A.B.E","Fau lt"))

It is only nested IF statements.
You need the AND operator to test both of the expressions in each statement, ie if A1 equals A while at the same time B1 equals A.B.C then display 'Fault'.

[Goddess of Whatever]

No, it doesn't work, lom.

[RDN]

Try this one: (no line breaks, obviously)

=OR(
IF(A1="A",IF(B1="A.B.C",TRUE),FALSE),
IF(A1="B",IF(B1="A.B.D",TRUE),FALSE),
IF(A1="C",IF(B1="A.B.E",TRUE),FALSE)
)

This returns "true" if:

if A1=A and B1=A.B.C
OR
if A1=B and B1=A.B.D
OR
if A1=C and B1=A.B.E

Is that what you meant?