Using the AVERAGE Function

[Guest]

I have a spreadsheet listing the months of the year in A12:A23. In O12:O23,
I am capturing a QA rating for our call center. Cell O24 contains the
following formula: =IF(SUM(O12:O23)<>0,AVERAGE(O12:O23),"") which gives me
the average QA rating for the year. My problem is that since we are only
through the month of January, the total in O24 won't make sense until after I
input every month's QA numbers. Is there a way to tweak this formula so that
will give me an accurate QA average as the year progresses?

 

[Guest]

I'm not quite sure I know what you mean by an 'Accurate' QA average, because
you won't have the entire year average until the year is in fact over. The
only sense I can make out of your problem is you have the cells for months
that haven't occured yet populated with a 0 value. In this case AVERAGE()
will take these into consideration. If this is the case, you can do a couple
things:

1) Instead of putting 0 in the month cells that haven't occured yet, make
them blank.
2) Change your formula to the following and commit with CTRL+SHIFT+ENTER:
=IF(SUM(O12:O23)<>0,AVERAGE(IF(O12:O23<>0,O12:O23)),"")

Does that help?

 

[Bob Phillips]

=AVERAGE(IF((MONTH(A12:A23)<=MONTH(TODAY())*(A12:A23<>"")),O12:O23))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks, Dave! This works perfect!