A
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.
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.