Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding noncontiguous cells | Excel Discussion (Misc queries) | |||
noncontiguous cells | Excel Discussion (Misc queries) | |||
Adding one month to noncontiguous cells | Excel Worksheet Functions | |||
maximum for noncontiguous cells in chart? | Charts and Charting in Excel | |||
can you use countif function for noncontiguous cells | Excel Worksheet Functions |