Excel Formula needed

Question

In my spreadsheet cells AA9:AB9, AD9:AE9 and AH9:AL9 have a maximum value of three (3). Cells AC9 and AF9:AG9 have a maximum value of five.  I need a formula that returns a value of 1 if any of the cells AA9:AL9 are below the maximum value.

Answer 1

Hope this helps

=--(OR(AA9:AB9<3,AD9:AE9<3,AH9:AL9<3,AF9:AG9<5))

=--(AND(OR(AA9:AB9<3,AD9:AE9<3,AH9:AL9<3,AF9:AG9<5),AA9:AB9<>"",AD9:AL9<>""))

=--(AND(OR(AA9:AB9<3,AD9:AE9<3,AH9:AL9<3,AF9:AG9<5),CONCAT(AA9:AL9)<>""))

Answer 2

Try this:

=--(OR(AA9:AB9<3,AD9:AE9<3,AH9:AL9<3,AF9:AG9<5))

This has to be put in as a array formula, by pressing Ctrl+Shift+Enter

The formula will be enclosed in curly brackets { }

See Image

Answer 3

Thank you so much.  This formula worked perfectly.  One final question if I wanted it to return a value of 0 if cells AA9:AL10 were blank what would I need to add to the formula.

Answer 4

AA9	AB9	AC9	AD9	AE9	AF9	AG9	AH9	AI9	AJ9	AK9	AL9
3	3	5	3	3	5	5	3	3	3	3	3

Here is an example of my data.  AA9:AB9, AD9:AE9,  and AH9:AL9 = 3, and AC9, and AF9:AG9 = 5.  If any of the cells fell below their maximum value the total returned by the formula would be 1.  These figures represent the maximum value for these cell or questions.

In the example below cell AB9 is two which is below the maximum value of three so I would want the count to be zero.  Each cell is the result of an audit and the premise is that if one question or cell falls below maximum value the entire transaction is considered a fail.  If more than one cell falls below value the output is still one.

AA9	AB9	AC9	AD9	AE9	AF9	AG9	AH9	AI9	AJ9	AK9	AL9
3	2	5	3	3	5	5	3	3	3	3	3

Here is an example where multiple cells AB9 and AF9 are below their maximum values and the expected outcome is still 1.  Hope this is clearer.

AA9	AB9	AC9	AD9	AE9	AF9	AG9	AH9	AI9	AJ9	AK9	AL9
3	2	5	3	3	4	5	3	3	3	3	3

Answer 5

Hi,

You question is not realy clear, can you post some sample data and the result you want to get. Are the values in AC9 & AF9:AFAG9 related to specific ranges to evaluate or do you mean evaluate the whole range AA9:AL9.