10

I have a few sensor readings and I'm checking to see if the values are in the acceptable range.

To do this I use an IF to check if the values are > or < the calculated (average) value. The results are stored in the respective columns. Finally I sum the results to get the count of how many are out of bounds (i.e. above the average).

For example, Ax is compared with Mean.Ax to get either 1 or 0 in If value is outside accepted bounds.Ax:

Image 1

Then the sum of If value is outside accepted bounds.Ax is performed to get Number of values outside bound.Ax:

Image 2, summation

Question
How do I convert this into a single formula?

Improve this question
0

2 Answers 2

Reset to default
11

The function you are after is COUNTIF():

Worksheet Screenshot

Enter the following formula in G3 and ctrl-enter/copy-paste/fill-right into G3:I3:

=COUNTIF(A3:A8,">"&D3)

COUNTIF() checks each value in the first argument against the criteria in the second one, and counts the number of times that it is met.

Using COUNTIF() is the simplest and best solution.

Of course, you could use a more complicated/harder to understand formula like

=SUMPRODUCT(--(A3:A8>D3))

or an array entered one like

{=SUM(--(A3:A8>D3))}

or even a more unnecessarily complicated version of those.

However, there is no benefit to be had by using any of those in this particular case.


If fact, since you seem to be interested in reducing the number of helper columns, an even better overall solution would be to dispense with the Mean helper columns as well:

Worksheet Screenshot

Enter the following formula in D3 and ctrl-enter/copy-paste/fill-right into D3:F3:

=COUNTIF(A3:A8,">"&AVERAGE(A3:A8))

(And yes, this formula could also be made harder to understand for a beginner by converting it to =SUMPRODUCT(--(A3:A8>AVERAGE(A3:A8))) or {=SUM(--(A3:A8>AVERAGE(A3:A8)))}.)

Improve this answer
3
  • @AFH Thanks for the edit (and the upvote ;-) ). I wonder how that happened? And more importantly, how I failed to notice it ¯\_(ツ)_/¯
    – robinCTS
    Commented Jul 3, 2018 at 12:41
  • Congrats. The system bots select highly rated posts to use as audits in the LQP review queue. This one was selected as a "low quality" post -- a badge of honor. :-)
    – fixer1234
    Commented Jul 26, 2018 at 19:23
  • @fixer1234 Oh, of course (－‸ლ) I give three alternative solutions for the Y-problem and then proceed to supply an X-problem solution. And on top of that I offer up an explanation of how the COUNTIF() function works. Clearly this answer is Low Quality! Next time I'll just stick to a single line, code only answer. (PS Just checked the timeline. Looks like you failed the audit - you selected Looks OK ;-) )
    – robinCTS
    Commented Jul 27, 2018 at 2:52
2

SUMPRODUCT Function can solve your problem also.

enter image description here

Write this formula in G102 & Fill it Right from G102 to I102:

=SUMPRODUCT(--(A102:A107>D102:D107))

N.B. Adjust the Cell address in the formula according to your need.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .