| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Function that ignores zeros
I am attempting to average three separate cells (B10, B22 and B34) and ignore
any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that ignores zeros
Try..
=SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0")) -- Jacob "Frustrated by Averages" wrote: I am attempting to average three separate cells (B10, B22 and B34) and ignore any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that ignores zeros
Try the below array formula. Apply using Ctrl+Shift+Enter instead of Enter
=AVERAGE(IF(MOD(ROW(B10:B34),12)=10,IF(B10:B34<0, B10:B34))) -- Jacob "Jacob Skaria" wrote: Try.. =SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0")) -- Jacob "Frustrated by Averages" wrote: I am attempting to average three separate cells (B10, B22 and B34) and ignore any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that ignores zeros
That worked. Thanks!!
"Jacob Skaria" wrote: Try.. =SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0")) -- Jacob "Frustrated by Averages" wrote: I am attempting to average three separate cells (B10, B22 and B34) and ignore any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that ignores zeros
Thanks again. One more question...Is there any easy way to hide a "#DIV/0"
error? As the spreadsheet is populated it will go away, so this is more viewing purposes while the spreadsheet is blank. "Jacob Skaria" wrote: Try.. =SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0")) -- Jacob "Frustrated by Averages" wrote: I am attempting to average three separate cells (B10, B22 and B34) and ignore any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that ignores zeros
Thanks again! That worked. Can you give me brief explanation of how this
formula is constructed as I wold like to recreate it in other scenarios? Thanks! "Jacob Skaria" wrote: Try the below array formula. Apply using Ctrl+Shift+Enter instead of Enter =AVERAGE(IF(MOD(ROW(B10:B34),12)=10,IF(B10:B34<0, B10:B34))) -- Jacob "Jacob Skaria" wrote: Try.. =SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0")) -- Jacob "Frustrated by Averages" wrote: I am attempting to average three separate cells (B10, B22 and B34) and ignore any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that ignores zeros
What version of Excel are you using?
Will there be any negative numbers in the cells? What result do you want to replace the error? -- Biff Microsoft Excel MVP "Frustrated by Averages" wrote in message ... Thanks again. One more question...Is there any easy way to hide a "#DIV/0" error? As the spreadsheet is populated it will go away, so this is more viewing purposes while the spreadsheet is blank. "Jacob Skaria" wrote: Try.. =SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0")) -- Jacob "Frustrated by Averages" wrote: I am attempting to average three separate cells (B10, B22 and B34) and ignore any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that ignores zeros
I am using 2007. There are no negative numbers. I would prefer that the
cell be blank in place of the error code. Thanks. "T. Valko" wrote: What version of Excel are you using? Will there be any negative numbers in the cells? What result do you want to replace the error? -- Biff Microsoft Excel MVP "Frustrated by Averages" wrote in message ... Thanks again. One more question...Is there any easy way to hide a "#DIV/0" error? As the spreadsheet is populated it will go away, so this is more viewing purposes while the spreadsheet is blank. "Jacob Skaria" wrote: Try.. =SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0")) -- Jacob "Frustrated by Averages" wrote: I am attempting to average three separate cells (B10, B22 and B34) and ignore any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that ignores zeros
Try this...
=IFERROR(SUM(B10,B22,B34)/INDEX(FREQUENCY((B10,B22,B34),0),2),"") -- Biff Microsoft Excel MVP "Frustrated by Averages" wrote in message ... I am using 2007. There are no negative numbers. I would prefer that the cell be blank in place of the error code. Thanks. "T. Valko" wrote: What version of Excel are you using? Will there be any negative numbers in the cells? What result do you want to replace the error? -- Biff Microsoft Excel MVP "Frustrated by Averages" wrote in message ... Thanks again. One more question...Is there any easy way to hide a "#DIV/0" error? As the spreadsheet is populated it will go away, so this is more viewing purposes while the spreadsheet is blank. "Jacob Skaria" wrote: Try.. =SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0")) -- Jacob "Frustrated by Averages" wrote: I am attempting to average three separate cells (B10, B22 and B34) and ignore any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that ignores zeros
That worked. Thanks!
"T. Valko" wrote: Try this... =IFERROR(SUM(B10,B22,B34)/INDEX(FREQUENCY((B10,B22,B34),0),2),"") -- Biff Microsoft Excel MVP "Frustrated by Averages" wrote in message ... I am using 2007. There are no negative numbers. I would prefer that the cell be blank in place of the error code. Thanks. "T. Valko" wrote: What version of Excel are you using? Will there be any negative numbers in the cells? What result do you want to replace the error? -- Biff Microsoft Excel MVP "Frustrated by Averages" wrote in message ... Thanks again. One more question...Is there any easy way to hide a "#DIV/0" error? As the spreadsheet is populated it will go away, so this is more viewing purposes while the spreadsheet is blank. "Jacob Skaria" wrote: Try.. =SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0")) -- Jacob "Frustrated by Averages" wrote: I am attempting to average three separate cells (B10, B22 and B34) and ignore any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. . . |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function that ignores zeros
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Frustrated by Averages" wrote in message ... That worked. Thanks! "T. Valko" wrote: Try this... =IFERROR(SUM(B10,B22,B34)/INDEX(FREQUENCY((B10,B22,B34),0),2),"") -- Biff Microsoft Excel MVP "Frustrated by Averages" wrote in message ... I am using 2007. There are no negative numbers. I would prefer that the cell be blank in place of the error code. Thanks. "T. Valko" wrote: What version of Excel are you using? Will there be any negative numbers in the cells? What result do you want to replace the error? -- Biff Microsoft Excel MVP "Frustrated by Averages" wrote in message ... Thanks again. One more question...Is there any easy way to hide a "#DIV/0" error? As the spreadsheet is populated it will go away, so this is more viewing purposes while the spreadsheet is blank. "Jacob Skaria" wrote: Try.. =SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0")) -- Jacob "Frustrated by Averages" wrote: I am attempting to average three separate cells (B10, B22 and B34) and ignore any zeros that exist. Each cell contains a formula that is gathering information from a pivot table. I need the average formula to ignore any zeros that may be in these cells when calculating the average. I have tried many different formulas, but nothing seems to work. All of the solutions I have read related to this problem assume the cells are in a continuous range (e.g. B10;B34). Any help would be appreciated. . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average Function (include Blank Cells and Zeros) | Excel Discussion (Misc queries) | |||
| VBA Function that ignores dates in a Holiday Table | Excel Worksheet Functions | |||
| Average a set of figures which ignores 0 entries | Excel Worksheet Functions | |||
| Sum function which ignores hidden rows | Excel Worksheet Functions | |||
| How do i get an average that ignores blanks in the range of cells. | Excel Worksheet Functions | |||
Linear Mode
