| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to average data if in cells, but ignore blank cells.
1. I am averaging several columns, but when all blanks, comes back #DIV/)!.
2. I then have to average the rows, so when a row has a number in it and a #DIV/)! in it, the return I get is #DIV/0!. 3. What I need it to do is the columns return a blank cell when I average them if they are blank. I need the rows to return the average on the numbers only and return a blank cell if no numbers in that row. Also all of this info is carried with other info onto a master sheet. This is what it looks like: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarte AnnualAverag 97% 97% 94% 94% 100% 100% #DIV/0! 97% #DIV/0! #DIV/0! #DIV/0! #DIV/0! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to average data if in cells, but ignore blank cells.
Try this type of formula:
=AVERAGE(IF(INDEX(A2:D2<"",),A2:D2)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Paula" wrote in message ... 1. I am averaging several columns, but when all blanks, comes back #DIV/)!. 2. I then have to average the rows, so when a row has a number in it and a #DIV/)! in it, the return I get is #DIV/0!. 3. What I need it to do is the columns return a blank cell when I average them if they are blank. I need the rows to return the average on the numbers only and return a blank cell if no numbers in that row. Also all of this info is carried with other info onto a master sheet. This is what it looks like: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarte AnnualAverag 97% 97% 94% 94% 100% 100% #DIV/0! 97% #DIV/0! #DIV/0! #DIV/0! #DIV/0! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to average data if in cells, but ignore blank cells.
RD,
I did what you suggested, but it returned a 0%, so then when averaged, it averaged in the 0%. Any suggestions? Thank you. "RagDyeR" wrote: Try this type of formula: =AVERAGE(IF(INDEX(A2:D2<"",),A2:D2)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Paula" wrote in message ... 1. I am averaging several columns, but when all blanks, comes back #DIV/)!. 2. I then have to average the rows, so when a row has a number in it and a #DIV/)! in it, the return I get is #DIV/0!. 3. What I need it to do is the columns return a blank cell when I average them if they are blank. I need the rows to return the average on the numbers only and return a blank cell if no numbers in that row. Also all of this info is carried with other info onto a master sheet. This is what it looks like: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarte AnnualAverag 97% 97% 94% 94% 100% 100% #DIV/0! 97% #DIV/0! #DIV/0! #DIV/0! #DIV/0! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to average data if in cells, but ignore blank cells.
Use this kind of formula in place of all your average formulas:
=IF(COUNT(B2:B4)0,AVERAGE(B2:B4),"") HTH, Bernie MS Excel MVP "Paula" wrote in message ... 1. I am averaging several columns, but when all blanks, comes back #DIV/)!. 2. I then have to average the rows, so when a row has a number in it and a #DIV/)! in it, the return I get is #DIV/0!. 3. What I need it to do is the columns return a blank cell when I average them if they are blank. I need the rows to return the average on the numbers only and return a blank cell if no numbers in that row. Also all of this info is carried with other info onto a master sheet. This is what it looks like: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarte AnnualAverag 97% 97% 94% 94% 100% 100% #DIV/0! 97% #DIV/0! #DIV/0! #DIV/0! #DIV/0! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to average data if in cells, but ignore blank cells.
Thank you Bernie Deitrick-that did it!!
"Bernie Deitrick" wrote: Use this kind of formula in place of all your average formulas: =IF(COUNT(B2:B4)0,AVERAGE(B2:B4),"") HTH, Bernie MS Excel MVP "Paula" wrote in message ... 1. I am averaging several columns, but when all blanks, comes back #DIV/)!. 2. I then have to average the rows, so when a row has a number in it and a #DIV/)! in it, the return I get is #DIV/0!. 3. What I need it to do is the columns return a blank cell when I average them if they are blank. I need the rows to return the average on the numbers only and return a blank cell if no numbers in that row. Also all of this info is carried with other info onto a master sheet. This is what it looks like: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarte AnnualAverag 97% 97% 94% 94% 100% 100% #DIV/0! 97% #DIV/0! #DIV/0! #DIV/0! #DIV/0! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I average a group of cells and ignore a #div/0! error? | Excel Discussion (Misc queries) | |||
| Generating blank or null cells that the Histogram Data Analysis tool will ignore | Excel Worksheet Functions | |||
| ignore blank cells | Excel Discussion (Misc queries) | |||
| how can i ignore blank cells when multiple cells? | Excel Worksheet Functions | |||
| How do I ignore cells with errors when calculating an average? | Excel Worksheet Functions | |||
Hybrid Mode
