• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Average a range of numbers based on multiple conditions

AWB

New Member
Hello, I have looked for a few hours for a solution and am quite stuck on this one! Any help would be much appreciated. :)

I am looking to find the average of all the cells in column A that meet the following conditions. If they don't meet them, they don't get averaged.
  • Anytime column A is greater than 0.5, average it (regardless of anything else)
  • Anytime column B is greater than 6, average corresponding column A cell (regardless of anything else. e.g. col A cell could be less than 0.5)
  • Another way you could put this is average all of column A except the cells where A < 0.5 AND B < 6
For more information:
  • column A = wind speed
  • column B = temperature
For more information about the problem, I do data analysis for meteorological wind towers. Sometimes the sensors that are spinning to measure the wind speed on the towers freeze solid and stop spinning. I need to be able to exclude the data if the wind speed drops below 0.5 UNLESS the temperature is greater than 6. If the temperate is greater than 6 C, it isn't frozen and it's just a calm day with no wind.

Thank you!!
 

Attachments

  • Icing Data Problem.xlsx
    8.5 KB · Views: 6
You could introduce a helper column to flag invalid readings.
Otherwise subtract the number of invalid readings from the total number and use that to form the average.
= SUM(WS) / ( COUNT(WS) - COUNTIFS( WS, "< 5", T, "< 6") )
 

Attachments

  • Icing Data Problem.xlsx
    13 KB · Views: 4
Peter Bartholomew
About Sum(WS) ...
Is it sure that WS are always as 'zero' then not Your valid?
if any of B8, B12 or B14 are greater than 0.5 ... those would raise average or how?
 
Agreed. I observed that the invalid readings were all zero and built it into the formula as an assumption. I should only have done that had the condition had been =0 (frozen solid)
= SUM(WS) / ( COUNT(WS) - COUNTIFS( WS, 0, T, "< 6") )
otherwise
= (SUM(WS) - SUMIFS( WS, WS, "<0.5", T, "< 6"))
/ (COUNT(WS) - COUNTIFS( WS, "<0.5", T, "< 6"))

Since I always use array formulas, I could also settle for
= AVERAGE( IF((WS>0.5)+(T>6), WS) )
Because traditional spreadsheets make a mess of arrays, I would have placed the array calculation within a named formula rather than as a grid formula.
 

Attachments

  • Icing Data Problem.xlsx
    15.5 KB · Views: 1
  • Love
Reactions: AWB
Without helper columns/names etc.:
=AVERAGE(IF((B2:B13>=6)+(A2:A13>=0.5),A2:A13))
which, because it looks like you have Excel 2007, may need to be committed to the sheet with Ctrl+Shift+Enter rather than the more usual Enter. If you don't do this it won't show an error but it will give the wrong answer (~3.9 instead of ~5.2).

Note that these two statements of yours don't quite agree:
Anytime column A is greater than 0.5, average it (regardless of anything else)
column A except the cells where A < 0.5
so you may need to tweak the formula a tiny bit by deleting an = sign.
 
Last edited:
Peter - you are fantastic and this works wonderfully!

p45cal - I believe the statements aren't contradictory, one mentions average if it is above 0.5 and the other mentions don't average if below 0.5 :)
 
p45cal - I believe the statements aren't contradictory, one mentions average if it is above 0.5 and the other mentions don't average if below 0.5 :)
Take a value of exactly 0.5:
With your first statement:
"Column A is greater than 0.5, average it"
it won't be averaged, because 0.5 is not greater than 0.5

With your second statement:
"average all of column A except the cells where A < 0.5 "
it will be averaged because 0.5 is not < 0.5, that is, it doesn't fall into the exception.

Try it on sheet:
72340

To correct this either change the first statement to say "Column A is equal to or greater than 0.5, average it"
OR
change the second statement to say: "average all of column A except the cells where A <= 0.5 "
but don't do both.
 
Last edited:
Back
Top