Count number of cols which contain a value(s) in it less than x(array formula?)

[AC]

Hi

We have columns of yearly data. Sometimes the data falls below a
critical cutoff, and if it does we want to flag that year as having an
issue and add it to the count of years with issues. If a year has
more than one issue we dont care, we just want to know that the year
had a problem. So if a column had 3 critical values in it we still
only count 1.

We then want to know the total number of years with problems.

This could be done easily by a formula in each of the year columns
flagging if that year had a problem or not eg if(MIN(<column data>) <=
critical_value, 1,0)

However for various reasons we cannot do this and we need a single
formula over the entire table which works out whether each column in
the table has a critical_value problem and counts the number of
problems.

Is there a way to do this with a single formula? I wondered if array
formulas would help, but I cannot think of how to write it. [I know
something about array formulas but am really a novice at them]


Additionally: The data is getting sucked in from a system and
sometimes it has more/less years. How can the formula ignore missing/
0 data columns? I assume we check for the sum of the column being = 0
and if so ignore it (ie make it part of the condition - does column
sum > 0 AND a value falls beneath critical_value.


All help appreciated
AndyC

Please cc my email as well as post to newsgroup.

 

[Lars-Åke Aspelin]

Hi

We have columns of yearly data. Sometimes the data falls below a
critical cutoff, and if it does we want to flag that year as having an
issue and add it to the count of years with issues. If a year has
more than one issue we dont care, we just want to know that the year
had a problem. So if a column had 3 critical values in it we still
only count 1.

We then want to know the total number of years with problems.

This could be done easily by a formula in each of the year columns
flagging if that year had a problem or not eg if(MIN(<column data>) <=
critical_value, 1,0)

However for various reasons we cannot do this and we need a single
formula over the entire table which works out whether each column in
the table has a critical_value problem and counts the number of
problems.

Is there a way to do this with a single formula? I wondered if array
formulas would help, but I cannot think of how to write it. [I know
something about array formulas but am really a novice at them]


Additionally: The data is getting sucked in from a system and
sometimes it has more/less years. How can the formula ignore missing/
0 data columns? I assume we check for the sum of the column being = 0
and if so ignore it (ie make it part of the condition - does column
sum > 0 AND a value falls beneath critical_value.


All help appreciated
AndyC

Please cc my email as well as post to newsgroup.

Try this formula:

=MMULT(--(COLUMN(mydata)>0),--(MMULT(TRANSPOSE(--(mydata<critical_value)*(--(mydata<>""))),--(ROW(mydata)>0))>0))

Note: This is an array formula that should be entered by
CTRL+SHIFT+ENTER rather than just ENTER

mydata is a named range for you data
critical_value is a named range (cell) where the limit value is stored

The *(--(mydata<>"")) part takes care of the fact that we should
ignore columns within the data range that don't have any data.

Hope this helps / Lars-Åke