Averaging only populated cells

  • Thread starter Thread starter John
  • Start date Start date
J

John

I'm averaging cells that aren't next to each other, but i don't want the
average to include cells that aren't populated.

=AVERAGE(F4:F6,F10,F14,F18,F21)
f
F4- 5
F6- 5
F10-
F 14- 5

AVERAGE = 3.75

for example, its possible that, let say, F10 has no quantitiy, i don't want
that to drag my average down. I want the results in the example above to
average = 5
 
Well, try it out. AVERAGE ignores blank cells, so your result should
not be adversely affected.

Pete
 
Looks like you are free to drag, per Help on AVERGE.

a.. If an array or reference argument contains text, logical values, or
empty cells, those values are ignored; however, cells with the value zero
are included.

HTH
Regards,
Howard
 
The AVERAGE function does not include blank cells. If you want to use the
AVERAGE function and ignore any ZERO's.
Try something like this:

=AVERAGE(IF(F4<>0,IF(F6<>0,IF(F10<>0,IF(F14<>0,F4),F6),F10),F14))
 
it is including those cells in the average. it drags my average retail down
whether its blank or 0.
 
i take that back. your right. i was putting 0 in it. i just need to leave it
blank. t

thanks
 
thanks

WShelton said:
The AVERAGE function does not include blank cells. If you want to use the
AVERAGE function and ignore any ZERO's.
Try something like this:

=AVERAGE(IF(F4<>0,IF(F6<>0,IF(F10<>0,IF(F14<>0,F4),F6),F10),F14))
Click to expand...
 
Back
Top