Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cells with #DIV/0!
I have a WORKBOOK with series of monthly worksheets with a TOTALS worksheet
that pulls the values from the appropriate monthly worksheets which are input as each months numbers are added. The TOTALS worksheet needs to average only the monthly totals to date. The cells in the TOTALS worksheet display #DIV/0! until the corresponding worksheet is updated each month. How to I get an average of just the cells that contain values and not count the cells that display #DIV/0!, and do not have values yet? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cells with #DIV/0!
Try this array* formula:
=AVERAGE(IF(NOT(ISERROR(A1:A12)),A1:A12)) * An array formula has to be committed using CTRL-SHIFT-ENTER (CSE) rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. Use CSE again whenever you edit the formula. Obviously, adjust the ranges to suit your situation. Hope this helps. Pete On Jun 19, 2:41*pm, Keller2 wrote: I have a WORKBOOK with series of monthly worksheets with a TOTALS worksheet that pulls the values from the appropriate monthly worksheets which are input as each months numbers are added. *The TOTALS worksheet needs to average only the monthly totals to date. The cells in the TOTALS worksheet display #DIV/0! until the corresponding worksheet is updated each month. How to I get an average of just the cells that contain values and not count the cells that display #DIV/0!, and do not have values yet? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cells with #DIV/0!
Say we have data in A1 thru A10, but some may have errors
=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) an array formula placed with CNTRL-SHFT-ENTER rather than just the ENTER key will exclude errors. -- Gary''s Student - gsnu200793 "Keller2" wrote: I have a WORKBOOK with series of monthly worksheets with a TOTALS worksheet that pulls the values from the appropriate monthly worksheets which are input as each months numbers are added. The TOTALS worksheet needs to average only the monthly totals to date. The cells in the TOTALS worksheet display #DIV/0! until the corresponding worksheet is updated each month. How to I get an average of just the cells that contain values and not count the cells that display #DIV/0!, and do not have values yet? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cells with #DIV/0!
It worked!!!! Thank you so very much.
"Pete_UK" wrote: Try this array* formula: =AVERAGE(IF(NOT(ISERROR(A1:A12)),A1:A12)) * An array formula has to be committed using CTRL-SHIFT-ENTER (CSE) rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. Use CSE again whenever you edit the formula. Obviously, adjust the ranges to suit your situation. Hope this helps. Pete On Jun 19, 2:41 pm, Keller2 wrote: I have a WORKBOOK with series of monthly worksheets with a TOTALS worksheet that pulls the values from the appropriate monthly worksheets which are input as each months numbers are added. The TOTALS worksheet needs to average only the monthly totals to date. The cells in the TOTALS worksheet display #DIV/0! until the corresponding worksheet is updated each month. How to I get an average of just the cells that contain values and not count the cells that display #DIV/0!, and do not have values yet? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average cells with #DIV/0!
You're welcome - thanks for feeding back.
Pete On Jun 19, 3:05*pm, Keller2 wrote: It worked!!!! Thank you so very much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average cells | Excel Discussion (Misc queries) | |||
from a group of cells.find average of cells containing values | Excel Discussion (Misc queries) | |||
Excel-only average cells if two cells in same row, meet two condit | Excel Worksheet Functions | |||
average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
average 2 cells (Mileage Divide by Gallons in two cells | Excel Discussion (Misc queries) |