average of 12 noncontiguous cells
 

Hi all -

I need to find the average of 12 noncontigouse cells
Each cell to be included only if the value of the cell is 0

If these cell were contigous I could use a sumif/countif formula

Not sure how to proceed for noncontiguous
These cells for example
=+L11+R11+W11+AB11+AH11+AM11+AR11+AX11+BC11+BH11+B N11+BQ11

Thx
Best regards,
-markc

average of 12 noncontiguous cells
 

they are all in the same row, right? can't you sumif(the row,"0") divided
by countif(the row, "0")


"goss" wrote:

Hi all -

I need to find the average of 12 noncontigouse cells
Each cell to be included only if the value of the cell is 0

If these cell were contigous I could use a sumif/countif formula

Not sure how to proceed for noncontiguous
These cells for example
=+L11+R11+W11+AB11+AH11+AM11+AR11+AX11+BC11+BH11+B N11+BQ11

Thx
Best regards,
-markc

average of 12 noncontiguous cells
 

Try this

=AVERAGE(L11,R11,W11,AB11,AH11,AM11,AR11,AX11,BC11 ,BH11,BN11,BQ11)

Peter

"goss" wrote:

Hi all -

I need to find the average of 12 noncontigouse cells
Each cell to be included only if the value of the cell is 0

If these cell were contigous I could use a sumif/countif formula

Not sure how to proceed for noncontiguous
These cells for example
=+L11+R11+W11+AB11+AH11+AM11+AR11+AX11+BC11+BH11+B N11+BQ11

Thx
Best regards,
-markc

average of 12 noncontiguous cells
 

But the OP had a condition 0
This is a prime example of a not so good spreadsheet design

Anyway here's an ugly way

=SUM(SUMIF(INDIRECT({"D8","G8","I8","K8","P8","R8" }),"0"))/SUM(COUNTIF(INDIRECT({"D8","G8","I8","K8","P8","R8 "}),"0"))


the OP needs to adapt it to his requirement



--


Regards,


Peo Sjoblom



"Billy Liddel" wrote in message
...
Try this

=AVERAGE(L11,R11,W11,AB11,AH11,AM11,AR11,AX11,BC11 ,BH11,BN11,BQ11)

Peter

"goss" wrote:

Hi all -

I need to find the average of 12 noncontigouse cells
Each cell to be included only if the value of the cell is 0

If these cell were contigous I could use a sumif/countif formula

Not sure how to proceed for noncontiguous
These cells for example
=+L11+R11+W11+AB11+AH11+AM11+AR11+AX11+BC11+BH11+B N11+BQ11

Thx
Best regards,
-markc

average of 12 noncontiguous cells
 

I just tried your solution (which is great!) but found that, if all
source rows are 0 (or null) you get a #DIV/0 error.

Any suggestions on how to check for this error?

John.

average of 12 noncontiguous cells
 

One possible way (I am still using the cell references in my example)

=IF(SUM(COUNTIF(INDIRECT({"D8","G8","I8","K8","P8" ,"R8"}),"0"))=0,"",SUM(SUMIF(INDIRECT({"D8","G8", "I8","K8","P8","R8"}),"0"))/SUM(COUNTIF(INDIRECT({"D8","G8","I8","K8","P8","R8 "}),"0")))


will return a blank


=IF(SUM(COUNTIF(INDIRECT({"D8","G8","I8","K8","P8" ,"R8"}),"0"))=0,0,SUM(SUMIF(INDIRECT({"D8","G8"," I8","K8","P8","R8"}),"0"))/SUM(COUNTIF(INDIRECT({"D8","G8","I8","K8","P8","R8 "}),"0")))


will return 0


--


Regards,


Peo Sjoblom


"John Google" wrote in message
ups.com...
I just tried your solution (which is great!) but found that, if all
source rows are 0 (or null) you get a #DIV/0 error.

Any suggestions on how to check for this error?

John.

average of 12 noncontiguous cells
 

Peo,

Brilliant!

I should have thought of the IF function.

I just starting out with more complex formulas and find this newsgroup
very instructive.

John.