2

I have received an Excel file with the question to make a formula that calculates the average of a range that is not continuous, and when there are zero or blank values, that the formula doesn't take in account those cells.

The Excel file does look like this: enter image description here

We have to calculate an average for the morning (I2, I4, I6, I8, I10, I12 and I14) and evening (I3, I5, I7, I9, I11, I13 and I15). So I already figured out that I have to make a named range for this: enter image description here

Now I have found several sites which are telling me that I have to use AVERAGEIF, or COUNTIF, SUMIF,... but I'm always getting "#value".

enter image description here

What is the solution to do this calculation?

Edit: The file can be downloaded here: http://goo.gl/7k14Rm (File > download)

Improve this question
1
  • 1
    It might be excel dialect, but your averageif is correct for my version. Try using the cells rather than the named range, see if that works first.
    – Raystafarian
    Jan 21, 2014 at 15:14

1 Answer 1

Reset to default
3

You can use AVERAGEIFS perhaps?

=AVERAGEIFS(I2:I15;I2:I15;"<>0";C2:C15;"Ochtend")

This is for Ochtend.

For Avond:

=AVERAGEIFS(I2:I15;I2:I15;"<>0";C2:C15;"Avond")

So that it will calculate the average of I2:I15 only for values not zero and for those corresponding to Avond.

Improve this answer
0

You must log in to answer this question.

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