Excel set 1 or 0 IF any value from array is between two values from treshold

Question

There is a dynamic array in Excel with some values.

Values could change based on some formulas (doesn't matter which formulas).

There are tresholds (0-31, 61-85 and so on).

Based on these tresholds if any value from the first row of the array is between 0-31 then CM-11 should be 1, else 0.

For CM-12 we should take 31-61 and check if values 25,26,185,625 in this treshold or not. So in this case now it will be 0 since the criteria is false. And so on for every cell.

Is there any sophisticated approach to solve and automate this task applying Excel formulas? Or any other approach usingExcel?

Answer 1

Not a problem, I tried to put the images to help, you just need to adjust the correct cells

put a imput cell, a base value and a upper value to define the condition and put in the cell below

=uppercell + 30

Then use the following formula:

=if(and(base cell < input; imput < upper cell); 1; 0) 

Answer 2

Using this formula, as displayed in the further down image, you can count items in a range that are between thresholds:

=COUNTIFS($B$2:$D$4,"<"&F9,$B$2:$D$4,">"&E9)

Note the "<"& tied to a cell reference...

Checked for my small array if values were between 0 & 2, 2 & 4, 4 & 10, and 10 & 11.