| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging blank or cells with Zeros in them.
I am trying to average ten cells. Some of the cells have numbers that were
totals of two cells. "Sample" =E4+F5 total that will go to a separate cell H5. There will be ten cells H5:Q5 that will collect my input. Some will be blank or will have zeros in them.My problem is getting a correct average. I have tried several formulas see below. =SUM(D3:D15)/COUNTIF(D3:D15,"0") =IF(COUNT(B1:B10)0,AVERAGE(B1:B10),"") =AVERAGE(IF(A2:A5,A2:A5,A2:A5)) =SUMPRODUCT(--(B5:D5),(B2:D2))/(IF(ISBLANK(B5),0,B2)+IF(ISBLANK(C5),0,C2)+IF(ISBL ANK(D5),0,D2)) =SUM(D33,G33,J33,M33,P33,S33,V33,Y33)/COUNT(D33,G33,J33,M33,P33,S33,V33,Y33) =AVERAGE(IF(ISNUMBER(B2:B6),B2:B6)) =IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE (D33,G33,J33,M33,P33,S33,V33,Y33),"") I have been working on this for several days. To no avail. If anyone has a suggestions I would be excited to try it. If there is a way for me to up load my practice worksheet to show you I would be more than happy to upload it or send it to you. Some of my verbiage may not be what you are used to reading. -- Thank You in advance. Glenn |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Averaging blank or cells with Zeros in them.
*Exactly* what cells do you want to average?
Hard to tell from all your sample formulas but I'm guessing you want to exclude cells with 0? -- Biff Microsoft Excel MVP "Glenn" wrote in message ... I am trying to average ten cells. Some of the cells have numbers that were totals of two cells. "Sample" =E4+F5 total that will go to a separate cell H5. There will be ten cells H5:Q5 that will collect my input. Some will be blank or will have zeros in them.My problem is getting a correct average. I have tried several formulas see below. =SUM(D3:D15)/COUNTIF(D3:D15,"0") =IF(COUNT(B1:B10)0,AVERAGE(B1:B10),"") =AVERAGE(IF(A2:A5,A2:A5,A2:A5)) =SUMPRODUCT(--(B5:D5),(B2:D2))/(IF(ISBLANK(B5),0,B2)+IF(ISBLANK(C5),0,C2)+IF(ISBL ANK(D5),0,D2)) =SUM(D33,G33,J33,M33,P33,S33,V33,Y33)/COUNT(D33,G33,J33,M33,P33,S33,V33,Y33) =AVERAGE(IF(ISNUMBER(B2:B6),B2:B6)) =IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE (D33,G33,J33,M33,P33,S33,V33,Y33),"") I have been working on this for several days. To no avail. If anyone has a suggestions I would be excited to try it. If there is a way for me to up load my practice worksheet to show you I would be more than happy to upload it or send it to you. Some of my verbiage may not be what you are used to reading. -- Thank You in advance. Glenn |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Averaging blank or cells with Zeros in them.
Hi,
You have all manner of cells referenced in your examples. Suppose your data is in cells A1:A10 here are a couple of approaches In 2007 =AVERAGEIF(A1:A10,"<0") In all versions if there are blanks or zeros: =SUM(A1:A10)/SUMPRODUCT(--(A1:A10<0)) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Glenn" wrote: I am trying to average ten cells. Some of the cells have numbers that were totals of two cells. "Sample" =E4+F5 total that will go to a separate cell H5. There will be ten cells H5:Q5 that will collect my input. Some will be blank or will have zeros in them.My problem is getting a correct average. I have tried several formulas see below. =SUM(D3:D15)/COUNTIF(D3:D15,"0") =IF(COUNT(B1:B10)0,AVERAGE(B1:B10),"") =AVERAGE(IF(A2:A5,A2:A5,A2:A5)) =SUMPRODUCT(--(B5:D5),(B2:D2))/(IF(ISBLANK(B5),0,B2)+IF(ISBLANK(C5),0,C2)+IF(ISBL ANK(D5),0,D2)) =SUM(D33,G33,J33,M33,P33,S33,V33,Y33)/COUNT(D33,G33,J33,M33,P33,S33,V33,Y33) =AVERAGE(IF(ISNUMBER(B2:B6),B2:B6)) =IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE (D33,G33,J33,M33,P33,S33,V33,Y33),"") I have been working on this for several days. To no avail. If anyone has a suggestions I would be excited to try it. If there is a way for me to up load my practice worksheet to show you I would be more than happy to upload it or send it to you. Some of my verbiage may not be what you are used to reading. -- Thank You in advance. Glenn |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Averaging blank or cells with Zeros in them.
Thank you for responding to me.
Sorry for my description. I am using the formula =SUM(D:5:D15)/COUNT(D3:D15,"0") This seems to be working well. Thank You Again Glenn "T. Valko" wrote: *Exactly* what cells do you want to average? Hard to tell from all your sample formulas but I'm guessing you want to exclude cells with 0? -- Biff Microsoft Excel MVP "Glenn" wrote in message ... I am trying to average ten cells. Some of the cells have numbers that were totals of two cells. "Sample" =E4+F5 total that will go to a separate cell H5. There will be ten cells H5:Q5 that will collect my input. Some will be blank or will have zeros in them.My problem is getting a correct average. I have tried several formulas see below. =SUM(D3:D15)/COUNTIF(D3:D15,"0") =IF(COUNT(B1:B10)0,AVERAGE(B1:B10),"") =AVERAGE(IF(A2:A5,A2:A5,A2:A5)) =SUMPRODUCT(--(B5:D5),(B2:D2))/(IF(ISBLANK(B5),0,B2)+IF(ISBLANK(C5),0,C2)+IF(ISBL ANK(D5),0,D2)) =SUM(D33,G33,J33,M33,P33,S33,V33,Y33)/COUNT(D33,G33,J33,M33,P33,S33,V33,Y33) =AVERAGE(IF(ISNUMBER(B2:B6),B2:B6)) =IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE (D33,G33,J33,M33,P33,S33,V33,Y33),"") I have been working on this for several days. To no avail. If anyone has a suggestions I would be excited to try it. If there is a way for me to up load my practice worksheet to show you I would be more than happy to upload it or send it to you. Some of my verbiage may not be what you are used to reading. -- Thank You in advance. Glenn |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Averaging blank or cells with Zeros in them.
Thank you for responding to me.
I am using Office 2003. I used this formula =SUM(D3:D15)/COUNTIF(D3:D15,"0") This worked well. I also used your formula and it also worked well. -- Thank y very much for your time. Glenn "Shane Devenshire" wrote: Hi, You have all manner of cells referenced in your examples. Suppose your data is in cells A1:A10 here are a couple of approaches In 2007 =AVERAGEIF(A1:A10,"<0") In all versions if there are blanks or zeros: =SUM(A1:A10)/SUMPRODUCT(--(A1:A10<0)) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Glenn" wrote: I am trying to average ten cells. Some of the cells have numbers that were totals of two cells. "Sample" =E4+F5 total that will go to a separate cell H5. There will be ten cells H5:Q5 that will collect my input. Some will be blank or will have zeros in them.My problem is getting a correct average. I have tried several formulas see below. =SUM(D3:D15)/COUNTIF(D3:D15,"0") =IF(COUNT(B1:B10)0,AVERAGE(B1:B10),"") =AVERAGE(IF(A2:A5,A2:A5,A2:A5)) =SUMPRODUCT(--(B5:D5),(B2:D2))/(IF(ISBLANK(B5),0,B2)+IF(ISBLANK(C5),0,C2)+IF(ISBL ANK(D5),0,D2)) =SUM(D33,G33,J33,M33,P33,S33,V33,Y33)/COUNT(D33,G33,J33,M33,P33,S33,V33,Y33) =AVERAGE(IF(ISNUMBER(B2:B6),B2:B6)) =IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE (D33,G33,J33,M33,P33,S33,V33,Y33),"") I have been working on this for several days. To no avail. If anyone has a suggestions I would be excited to try it. If there is a way for me to up load my practice worksheet to show you I would be more than happy to upload it or send it to you. Some of my verbiage may not be what you are used to reading. -- Thank You in advance. Glenn |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replacing blank cells with zeros | Excel Discussion (Misc queries) | |||
| Place zeros in blank cells | Excel Discussion (Misc queries) | |||
| sum of blank cells returns zeros | Excel Worksheet Functions | |||
| Averaging blank cells | Excel Discussion (Misc queries) | |||
| I want blank cells, but they're all zeros now that I have formatted them | Excel Discussion (Misc queries) | |||
Linear Mode
