Average Function Question

[TGV525]

I would like to get the average for a group of cells ( say X5:BX5 ) but i don't want the cells with zero to be included

I am counting attendance for an event that happens once a week and would like to find out what the average number of people attend over a year

for example week 1 has 15 people attending
week 2 has 20 people attending
the rest of the weeks still have 0 because they have not happened, is there a way to not include them until the # is greater than 0

thank you

 

[Ron Coderre]

Try this regular formula:

=SUM(X5:BX5)/COUNTIF(X5:BX5,">0")

is that something you can work with?

 

[JoeMo]

Here's one way:

=SUM(X5:BX5)/COUNTIF(X5:BX5,">0")

 

[TGV525]

Thank you, that worked

 

[T. Valko]

I would like to get the average for a group of cells ( say X5:BX5 ) but i don't want the cells with zero to be included

I am counting attendance for an event that happens once a week and would like to find out what the average number of people attend over a year

for example week 1 has 15 people attending
week 2 has 20 people attending
the rest of the weeks still have 0 because they have not happened, is there a way to not include them until the # is greater than 0

thank you

Here are a couple of other ways.

This array entered** formula will work in any version of Excel:

=AVERAGE(IF(X5:BX5>0,X5:BX5))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

This normally entered formula will work in Excel 2007 and later:

=AVERAGEIF(X5:BX5,">0")