Home |
Search |
Today's Posts |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi Treesy,
The average function will ignore blanks but not zero values. So if the formula in those cells is returning 0 alter it to return "" Alternatively this formula may help. =SUM(A1:A12)/COUNTIF(A1:A12,"0") adjust the ranges to suit. HTH Martin "Treesy" wrote in message ... Is there a way to not have a blank or zero value not be counted in an average function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Looking for conditional averaging or "Averageifs" function for | Excel Worksheet Functions | |||
Looking for conditional averaging or "Averageifs" function for | Excel Worksheet Functions | |||
Looking for conditional averaging or "Averageifs" function for '02 | Excel Worksheet Functions | |||
Averaging function is pulling back a zero in Excel | Excel Discussion (Misc queries) | |||
Averaging function | Excel Discussion (Misc queries) |