averaging selected cells excluding zero

Could someone please help me with my problem. I wanted to average selected cells excluding the value zero. Also, I wanted to make the value of cell is zero and not to display the error #DIV/0! if the denominator is zero.

A1 = Denominator

Selected cell:
A3, A5, A6, A8

Would really appreciate you're prompt reply and assistance. thanks

Try...

=IF(A1,SUM(A3,A5,A6,A8)/A1,0)

Get it please

=AVERAGE(IF(A1:A9<>0,A1:A9))

Comitted w/ CTRL+SHIFT+ENTER

Originally Posted by Domenic

Try...

=IF(A1,SUM(A3,A5,A6,A8)/A1,0)

Thank you very much for your prompt response. I was looking to my excel sheet and I didn't notice that the denominator should be the number of cells selected excluding zero and not to display the error if the denominator is zero.

Ex.

A1=0
A2=3
A3=2

the denominator should be only 2 since A1 is zero.

Are the relevant cells located in a contiguous range of cells, as in your last example? Or are they located in a non-contiguous range of cells, as in your original post?

Originally Posted by Domenic

Are the relevant cells located in a contiguous range of cells, as in your last example? Or are they located in a non-contiguous range of cells, as in your original post?

Its the former. As in my original post. thanks

Originally Posted by martindwilson

=sum(a3,a5,a6,a8)/sum(if(a3,1,0)+if(a5,1,0)+if(a6,1,0)+if(a8,1,0))
which is
=SUM(A3,A5,A6,A8)/SUM(IF(A3,1,0),IF(A5,1,0),IF(A6,1,0),IF(A8,1,0))

Wow this is the exact formula I needed thanks for your help

Assuming that the data does not contain negative numbers, here's another way...

=SUM(A1,A3,A5,A6,A8)/INDEX(FREQUENCY((A1,A3,A5,A6,A8),0),2)

Originally Posted by martindwilson

=sum(a3,a5,a6,a8)/sum(if(a3,1,0)+if(a5,1,0)+if(a6,1,0)+if(a8,1,0))
which is
=SUM(A3,A5,A6,A8)/SUM(IF(A3,1,0),IF(A5,1,0),IF(A6,1,0),IF(A8,1,0))

Hi, thank you for your response this is the exact formula I needed. But, I also wanted to make the value of cell to display 0 if the value of the denominator is 0 and to eliminate the error #DIV/0!. I wanted this because I'm going to change the datas every month and I don't want to change the formula if all the values in the cells are 0. thanks

Originally Posted by squiggler47

I think this takes care of your 0 problem!

=SUMPRODUCT(SUM(C2:H2),1/(SUM(--(C2:H2>0))+(SUM(--(C2:H2>0))=0)))

Hi thanks but the problem is the cells are in a non-contiguous range.

ex.

A1
A4
A6

here's the formula I used:

=SUM(W12,W17,W22,W27)/SUM(IF(W12,1,0)+IF(W17,1,0)+IF(W22,1,0)+IF(W27,1,0))

I wanted to eliminate the error #DIV/0! if the the cells are 0.

Try...

=SUM(W12,W17,W22,W27)/MAX(1,(W12<>0)+(W17<>0)+(W22<>0)+(W27<>0))

Originally Posted by Domenic

Try...

=SUM(W12,W17,W22,W27)/MAX(1,(W12<>0)+(W17<>0)+(W22<>0)+(W27<>0))

Thank you for this really appreciated.

So I've been working a report for almost a week now. Am kinda novice in using excel. And I still see some flaws to my computation. I hope somebody would help me with this.

Kindly refer to the attached file.

For B13, I wanted to average C13:G13 but to exclude the 0% from averaging if C13,D13,E14,F14,G14 = 0

ex. For D13, it is included in averaging since the value of D11 is 1
For E14, it is not included in the average since the the value of E11 is 0 so on and so forth..

You could work the average by excluding cells that are zero by summing the column, and then dividing by ceels different to 0.

The fomula would mean you would do a =SUM(A1:A20)/COUNTIF(A1:A20,"<>0") - for example

Originally Posted by bambino86

Thank you for this really appreciated.

So I've been working a report for almost a week now. Am kinda novice in using excel. And I still see some flaws to my computation. I hope somebody would help me with this.

Kindly refer to the attached file.

For B13, I wanted to average C13:G13 but to exclude the 0% from averaging if C13,D13,E14,F14,G14 = 0

ex. For D13, it is included in averaging since the value of D11 is 1
For E14, it is not included in the average since the the value of E11 is 0 so on and so forth..

hope someone could help me with my problem.

Somewhat unclear... Can you try to re-post your question, provide a representative sample of the data, and provide the actual result you expect.

Originally Posted by Domenic

For contiguous cells, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

Please Login or Register  to view this content.

With regards to non-contiguous cells, where are the relevant cells located?

Thanks Domenic. For non-contiguous cells C18, E18, and G18.

Originally Posted by Domenic

For non-contiguous cells, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

Please Login or Register  to view this content.

For a large range, where the pattern is consistent (every 2nd cell), try the following formula (adjust the range accordingly)...

Please Login or Register  to view this content.

...confirmed with CONTROL+SHIFT+ENTER

Wow you really lived up to your title "Forum Guro" very informative and helpful. Thank you for your help

Originally Posted by Domenic

For non-contiguous cells, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

Please Login or Register  to view this content.

For a large range, where the pattern is consistent (every 2nd cell), try the following formula (adjust the range accordingly)...

Please Login or Register  to view this content.

...confirmed with CONTROL+SHIFT+ENTER

Hi Domenic,

I'm sorry to trouble you again. I tried the formula and it works for excel 2007. But when I tried it in Excel 2003 the formula is not committing (CONTROL+SHIFT+ENTER is not working, the braces disappears) I don't know what am I doing wrong. Please help

When you confirmed with CONTROL+SHIFT+ENTER, did you first press down both the CONTROL and SHIFT keys, then while both keys were pressed down, press the ENTER key? Note that if you're using a Mac version of Excel (although I doubt it -- Excel 2003 is windows based), you'll need to confirm with COMMAND+RETURN instead. Have a look at the attached sample file.

Originally Posted by Domenic

When you confirmed with CONTROL+SHIFT+ENTER, did you first press down both the CONTROL and SHIFT keys, then while both keys were pressed down, press the ENTER key? Note that if you're using a Mac version of Excel (although I doubt it -- Excel 2003 is windows based), you'll need to confirm with COMMAND+RETURN instead. Have a look at the attached sample file.

Yes I first pressed down Control and Shift keys first and then hit enter. I opened your file and when I'm double clicking cell A2 and A4 the braces dissappear. is there something wrong with the excel version that i'm using?

Hi,

I am new to this forum pls help me out in posting the quiery..

Cheers,
PRIYA

Originally Posted by bambino86

Yes I first pressed down Control and Shift keys first and then hit enter. I opened your file and when I'm double clicking cell A2 and A4 the braces dissappear. is there something wrong with the excel version that i'm using?

I'm going offline now... but if you're double clicking A2 and A4, you'll need to re-confirm each one with CONTROL+SHIFT+ENTER.

Hi,

Pls help me in solving the excel where in i an preparing an Histroy detail for the training.

Hence i have doubt in solving which i have hightlighted in the red colour.

Pls help me out in solving it..

Cheers,
PRIYA

Originally Posted by Domenic

I'm going offline now... but if you're double clicking A2 and A4, you'll need to re-confirm each one with CONTROL+SHIFT+ENTER.

Hi Domenic,

Thanks for assistance. I have already resolved the problem with confirming the formula. I restarted my pc and and at last it did work. However, I noticed that the formula you provided to me still doesn't resolved the error DIV#0 if all the value is 0. Could I trouble you again to amend if possible the formula to resolve the error? I'm really desperate to solve the issue as I'm going to present a report using this formula to my big bosses. thank you

=AVERAGE(IF(C11:G11<>0,C13:G13))

Try...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,AVERAGE(IF(C11:G11<>0,C13:G13))))

...confirmed with CONTROL+SHIFT+ENTER.