Forum Discussion

Copper Contributor

Jan 26, 2024

How to check if all cells in a non congruent range/list meet specific criteria. See below.

I am trying to use the following formula to see if 1. All 39 cells in row C have data, 2. All cell values are greater than 225, and 3. All cell values are less then 384. If all conditions are met, t...

excel

OliverScheurich

Gold Contributor

Jan 26, 2024

spinaz0070

In my understanding all cells need to have values (condition 1) otherwise the values can't be greater than 225 (condition 2) and less than 384 (condition 3). If a cell doesn't have a value conditons 2 and 3 can't be true.

This formula checks for each uneven row if the cells in range C17:C93 have a value greater 225 and less than 384. If it is greater 225 and less than 384 the formula returns 1 otherwise 0. The SUM counts the 1s and 0s and the result must be 39 for the "PASS".

=IF(SUM(IF((C17:C93>225)*(C17:C93<384)*ISODD(ROW(C17:C93)),1,0))=39,"PASS","FAIL")

Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

spinaz0070

Copper Contributor

Jan 29, 2024

Hi Oliver. Can you please help me understand this formula. Why don't the first two conditions (>225, <384) need to call out the odd rows, and what do the ,1,0 do in the ISODD(ROW) condition? Just trying to learn the syntax. Thanks

OliverScheurich
Gold Contributor
Jan 29, 2024
spinaz0070
{TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE}

The above is the result of the evaluation of:
ISODD(ROW(C17:C93))
This means that the odd and even rows are determined by ISODD(ROW(C17:C93)) and this hasn't to be done again with the first two conditions.

This is the original formula.
=IF(SUM(IF((C17:C93>225)*(C17:C93<384)*ISODD(ROW(C17:C93)),1,0))=39,"PASS","FAIL")

This is the inner IF formula perhaps more readable with some spaces:
IF( (C17:C93>225)*(C17:C93<384)*ISODD(ROW(C17:C93)) , 1, 0)
The , 1, 0) aren't within the ISODD(ROW) condition.
Within IF this
(C17:C93>225)*(C17:C93<384)*ISODD(ROW(C17:C93))
is the condition which is checked and if it's true then 1 is returned and if false 0 is returned.

Below is what the evaluation of
IF( (C17:C93>225)*(C17:C93<384)*ISODD(ROW(C17:C93)) , 1, 0)
returns in the formula bar.