Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the AVERAGE Function
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? -- Tia, Education and Documentation Specialist |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the AVERAGE Function
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? -- Regards, Dave "Tia" wrote: 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? -- Tia, Education and Documentation Specialist |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the AVERAGE Function
=AVERAGE(IF((MONTH(A12:A23)<=MONTH(TODAY())*(A12:A 23<"")),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) "Tia" wrote in message ... 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? -- Tia, Education and Documentation Specialist |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the AVERAGE Function
Thanks, Dave! This works perfect!
-- Tia, Education and Documentation Specialist "David Billigmeier" wrote: 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? -- Regards, Dave "Tia" wrote: 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? -- Tia, Education and Documentation Specialist |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE function returns #DIV/0! error | Excel Discussion (Misc queries) | |||
Average function assistance | Excel Discussion (Misc queries) | |||
EXCEL 2000 AVERAGE function | Excel Worksheet Functions | |||
Using the average function | Excel Worksheet Functions | |||
How do I use an average function, not counting cells containing a | Excel Worksheet Functions |