Is there anyone who can solve this?...

[darno]

I am trying to write a formula by which I could be able to handle 1
conditions in total. Please help me out as Ms Excel can not handle mor
than 7 nested ifs. I have tried almost every thing, but no use. Pleas
show me a way out. How I can work around this situation.
My query is as follows. What I am trying to achieve is this:

Condition 1:

If the value in cell A1=81, and If the value in cell B1=21 then in cel
C3 show "good" else show "NE" (Remember the cell C3 is formatted b
conditional formatting which will turn to green if the value is GOOD i
the value is "NE" then show in yellow color.) Else it will be jus
white for any value.) Any ways conditional formatting is not an issue


Condition 2:

If the value in cell A1=82, and If the value in cell B1=21 then in cel
C3 show "BAD" else show "NE".

Condition 3:

If the value in cell A1=82, and If the value in cell B1=22 then in cel
C3 show "good" else show "NE"

Condition 4:

If the value in cell A1=83, and If the value in cell B1=21 then in cel
C3 show "BAD" else show "NE"

Condition 5:

If the value in cell A1=83, and If the value in cell B1=22 then in cel
C3 show "BAD" else show "NE"

Condition 6:

If the value in cell A1=83, and If the value in cell B1=23 then in cel
C3 show "good" else show "NE"

Condition 7:

If the value in cell A1=84, and If the value in cell B1=21 then in cel
C3 show "BAD" else show "NE"

Condition 8:

If the value in cell A1=84, and If the value in cell B1=22 then in cel
C3 show "BAD" else show "NE"

Condition 9:

If the value in cell A1=84, and If the value in cell B1=23 then in cel
C3 show "BAD" else show "NE"

Condition 10:

If the value in cell A1=84, and If the value in cell B1=24 then in cel
C3 show "BAD" else show "NE

 

[Frank Kabel]

Hi
one way (without using IF functions but lookup tables for two
conditions). Try the following
1. Setup your 2D lookup table (lets say on a sheet called 'lookup'). It
should look like the following
A B C D E
1 81 82 83 84
2 21 BAD BAD .. ...
3 22 GOOD .. .. .. ..
4 23
5 24

This matrix assigns to each combination of your two criteria on return
value (finish it according to the above outline)

On your sheet with the condition use the following formula (I assume
that 'NE' is return if no condition is fullfilled)

=IF(ISERROR(INDEX('lookup'!$B$2:$E$5,MATCH(B1,'lookup'!$A$2:$A$24,0),MA
TCH(A1,$B$1:$E$1,0))),"NE",INDEX('lookup'!$B$2:$E$5,MATCH(B1,'lookup'!$
A$2:$A$24,0),MATCH(A1,$B$1:$E$1,0)))

 

[Bernard Liengme]

Hi,
ON Sheet2 starting in A1 enter these values
good BAD BAD BAD
NE good BAD BAD
NE NE good BAD
NE NE NE BAD

In C3 of Sheet1 use
=IF(AND(A1-80>0,A1-80<5),IF(AND(B1-20>0,B1-20<5),INDEX(Sheet2!A14,B1-20,A1
-80),"NE"))
So if A1 is in range {81,82,83,84} and B1 is in {21,22,23,24} the formula
uses INDEX to lookup a value, else you get NE

 

[Norman Harker]

Hi Darno!

My response is to create the same sort of table as Frank but to use
VLOOKUP:

=VLOOKUP(A1,$H$2:$L$5,B1-19)

For detail see response I gave down the road at worksheet functions.

It helps if you only post to one group.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Tom Ogilvy]

See answer in programming