excel, average function

Question

Hi folks

how do I average(E7,L7,E24,L24,E31,L31) when not all of them will have values yet? ie E7 & L7 are 130 + 127 respectively but the 24's & 31's won't be have values until Mar, Apr, May & June?

There are values in adjoining cells that I don't want in the calc so I'm finding it tricky

Many thx

Answer 1

I suspect that Joeu2004 is correct that the contents of the cells are not as simple as you described. I am assuming now that there are formulas in those cells, probably a SUM function that adds up the intervening rows. If that is the case, then Excel doesn't see those cells as blank which is why it is dividing by 6 rather than by 2. You can use this formula to eliminate the cells that don't have a value yet so that Excel will divide by the correct number.

=AVERAGE(FILTER(VSTACK(E7,L7,E24,L24,E31,L31),VSTACK(E7,L7,E24,L24,E31,L31)<>0))

If the formulas in the cells result in "" rather than a 0, change the 0 at the end to "".

Answer 2

If you have only two values, the average is the sum of those two values divided by 2, not by 6. If you divide by 6, you count the missing values as 0.

If for some reason, you do really want to count the missing values as 6, use

=SUM(E7,L7,E24,L24,E31,L31)/6

Answer 3

That's what I thought but it's comes back with 43 (rounded). 130+127=257/6. I should be getting 128 (again rounded) Maybe it's because I'm still using 2007

Thanks for your time anyway

Answer 4

Simply =AVERAGE(E7,L7,E24,L24,E31,L31)

The AVERAGE function ignores empty cells and cells with text values.