Averageifs named ranges

[Tesnobay]

=averageifs(aprildata,maydata,junedata,"<>0")

Why doesn't this work?

 

[JoeMo]

You haven't told us what's in those named ranges, but from the names I'm guessing you are not using the correct arguments for AVERAGEIFS in your formula.

From Excel Help on AVERAGEIFS:
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The AVERAGEIFS function syntax has the following arguments:

• Average_range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
  
• Criteria_range1, criteria_range2, … Criteria_range1 is required, subsequent criteria_ranges are optional. 1 to 127 ranges in which to evaluate the associated criteria.
  
• Criteria1, criteria2, ... Criteria1 is required, subsequent criteria are optional. 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.
  

 

[Tesnobay]

I thought I did that

 

[Tesnobay]

I am averaging blood glucose reading for April, May, and June. June is not complete so thats why the "<>0" is in the formula. It works with Averageif for individual months, but doesn't for all three.

 

[OGCV1]

Tesnobay,

Easiest to select the whole data set and use AVERAGEIF()

 

[Tesnobay]

OGCV1, Didn't work, "too many arguments"

 

[OGCV1]

Tesnobay,

Yeah sorry was doing from memory and when I went to check it was wrong, serves me right I guess.
Is it possible to just select all the data and use an AVERAGEIF?

 

[Tesnobay]

The data for each month is not contiguous:
Apr = C4:AE6
May = C30:AG32
June = C56:AF58

Cells in June from 6/12- 6/30 currently show 0 until that days data is entered. that is why "<>0" is part of the formula.
Averageif works for an individual month, but when I put in all 3 ranges I get the too many arguments error message.

 

[Aladin Akyurek]

Care to post just C4:F6? This would help the helper to understand your layout...

 

[OGCV1]

You could always just use:
=SUM(aprildata,maydata,junedata)/SUM(COUNT(aprildata),COUNT(maydata),COUNTIF(junedata,"<>0"))