Nested If(and(...

  • Thread starter Thread starter darno
  • Start date Start date
D

darno

I am looking for a worksheet formula through which i should be able t
check more than 10 conditions with if(and(... as my requirement is t
check two cells, e.g. check if a3=10 and b3=7 then display in cel
address d3 "GOOD", else "OK". there are more than 10 conditions to b
checked. but i am unable to achieve all 11, i endup getting 7 correc
and rest it gives error in formula. May be excel do not allow us t
write more than 7 if(and(... formula. I am writing you the existin
formula which i have, but i am unable to add more conditions to it:

=IF(O
(I208>18;M208>66;O208>30;Q208>240;S208>90;U208>800);"FINE";IF(Y207="P";"good";IF(AND(AC207>=1;AC207<=7);IF(AC207=-AD207;IF(X208<>1;"BAD");"BAD");"BAD")))

This was my existing formula, if i were to add more conditions it won
work. Please help me out. The conditions are around 10, and i hav
already told you few of those, please help me out.


Regards,


Darn
 
There is indeed a limit to the number of arguments you can use in a cell.

Suggest you spread the formula over several columns. You will then need a
'results' column to analyse the various 'test' columns. Depending on your
requirement the output of test column A and C may be BAD and that of test
column B FINE. You will have to decide on what the actual output is of the
three results.

BTW it would be better if you reduced BAD and FINE to, say 0 (zero) and 1
(one). This could substantially reduce the size of a spreadsheet and
re-calculation.

Regards.

Bill Ridgeway
Computer Solutions
darno > said:
I am looking for a worksheet formula through which i should be able to
check more than 10 conditions with if(and(... as my requirement is to
check two cells, e.g. check if a3=10 and b3=7 then display in cell
address d3 "GOOD", else "OK". there are more than 10 conditions to be
checked. but i am unable to achieve all 11, i endup getting 7 correct
and rest it gives error in formula. May be excel do not allow us to
write more than 7 if(and(... formula. I am writing you the existing
formula which i have, but i am unable to add more conditions to it:

=IF(OR
Click to expand...
(I208>18;M208>66;O208>30;Q208>240;S208>90;U208>800);"FINE";IF(Y207="P";"good
 
You can't nest more than 7 if statements. Your conditions seem so unrelated
that it would be difficult to suggest any alternatives.

--
Regards,
Tom Ogilvy

darno > said:
I am looking for a worksheet formula through which i should be able to
check more than 10 conditions with if(and(... as my requirement is to
check two cells, e.g. check if a3=10 and b3=7 then display in cell
address d3 "GOOD", else "OK". there are more than 10 conditions to be
checked. but i am unable to achieve all 11, i endup getting 7 correct
and rest it gives error in formula. May be excel do not allow us to
write more than 7 if(and(... formula. I am writing you the existing
formula which i have, but i am unable to add more conditions to it:

=IF(OR
Click to expand...
(I208>18;M208>66;O208>30;Q208>240;S208>90;U208>800);"FINE";IF(Y207="P";"good
 
Hmmm... I made the substitutions you recommended and saw no decrease in
file size (plus or minus 128 bytes) nor change in calculation time (plus
or minus a millisecond), with a significant reduction in readability.
 
Perhaps a user defined function would provide the answer, this would allow
as many parameters as necessary and the VBA code would make checking and
changeing the conditional checking somewhat easier. However, I dont know if
there is a limit to the number of parameters that can be passed to the
function.

Just a thought

Peter

Tom Ogilvy said:
You can't nest more than 7 if statements. Your conditions seem so unrelated
that it would be difficult to suggest any alternatives.

--
Regards,
Tom Ogilvy

darno > said:
I am looking for a worksheet formula through which i should be able to
check more than 10 conditions with if(and(... as my requirement is to
check two cells, e.g. check if a3=10 and b3=7 then display in cell
address d3 "GOOD", else "OK". there are more than 10 conditions to be
checked. but i am unable to achieve all 11, i endup getting 7 correct
and rest it gives error in formula. May be excel do not allow us to
write more than 7 if(and(... formula. I am writing you the existing
formula which i have, but i am unable to add more conditions to it:

=IF(OR
Click to expand...
(I208>18;M208>66;O208>30;Q208>240;S208>90;U208>800);"FINE";IF(Y207="P";"good
Click to expand...
 
An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas the same way that you create basic, single-value formulas. Select the cell or cells that will contain the formula, create the formula, and then press CTRL+SHIFT+ENTER to enter the formula

This may help you? Check it out

Tim
 
Back
Top