CODEWITHSUNDEEP
excel
Hannah Griffith
Hannah Griffith

Reputation: 23

Excel - AVERAGEIFS function with ignoring NA query

Here is a simplified version of the data that I'm working with:

Column A (Year/Month/Day): 1955.0305,1955.0307,1955.0309,1955.0901,1956.0307,1956.0505

Column B (Sea-level Pressure): 1035.5,1028.1,1032,1025.6,1034.5,#N/A

I have data like this from 1955 to 2015 but the amount of data (rows) for each year varies.

I want to calculate the average of each year. Would using the AVERAGEIF function work? To state that I want it to average Column B where Column A starts with "1955" or "1956" and so on, up to "2015". However I also have #N/A missing data so would I need the AVERAGEIFS function to also exclude the #N/A cells from the averaging calculation?

If so, how would I write such a function? Is there even an easier way?

Thanks.

Upvotes: 2

Views: 15446

Answers (2)

w31hon9
w31hon9

Reputation: 61

You can do this:

AVERAGEIFS(average_range, criteria_range1, criteria, criteria_range2, "<>#N/A")

your criteria_range1 and criteria would be to satisfy your date constraints, and the criteria_range2 and "<>#N/A" helps to ignore #N/A where it exists

Example:

=AVERAGEIFS(P$8:P$2000,$H$8:$H$2000,$Q7,$P$8:$P$2000,"<>#N/A")

$Q7 is a month date, and H8:H2000 is daily dates. So my formula finds the monthly averages by ignoring any #N/A daily data

Upvotes: 4

pnuts
pnuts

Reputation: 59450

I know how I would handle this:

  1. Convert the date into something recognised as such by Excel, for example with:

    =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))

  2. Get rid of the #N/As (presumably text, so with Find/Replace - if not with Go To Special etc)

  3. Pivot the results. Not only average but other measures (max, min, stdev, count etc), not only by year but by month or by quarter etc, etc.

Upvotes: 2

Related Questions