Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
dlw dlw is offline
external usenet poster
 
Posts: 510
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 527
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 18
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 18
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding noncontiguous cells FJ Excel Discussion (Misc queries) 9 December 23rd 06 05:54 PM
noncontiguous cells Vivian Excel Discussion (Misc queries) 2 July 21st 06 01:00 AM
Adding one month to noncontiguous cells wmjenner Excel Worksheet Functions 3 June 15th 06 08:20 PM
maximum for noncontiguous cells in chart? doug86 Charts and Charting in Excel 3 January 17th 06 09:14 PM
can you use countif function for noncontiguous cells rutledbr Excel Worksheet Functions 2 November 13th 05 10:15 PM


All times are GMT +1. The time now is 04:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"