Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 26
Default Average non-adjacent cells & exclude zeros

I want to find an average of three cells that are non-adjacent and will
occasionally have a zero in it.
Example:
=AVERAGE(H12,V12,AJ12)

How can I rewrite this so that the formula will not include the cell in the
average if its value is zero?

Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default Average non-adjacent cells & exclude zeros

One way

=AVERAGE(IF((MOD(COLUMN(H12:AJ12),14)=8)*(H12:AJ12 <0),H12:AJ12))


needs to be entered with ctrl + shift & enter

this assumes you have the same number of columns between each value like you
have in your example


--


Regards,


Peo Sjoblom


"Danni2004" wrote in message
...
I want to find an average of three cells that are non-adjacent and will
occasionally have a zero in it.
Example:
=AVERAGE(H12,V12,AJ12)

How can I rewrite this so that the formula will not include the cell in
the
average if its value is zero?

Thanks!



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 26
Default Average non-adjacent cells & exclude zeros

Thanks Peo.
Can you clarify what the "8" is for in this formula?
Also, I've seen other posts with the Ctrl + Shift & Enter before. Can you
explain why you have to use this?

"Peo Sjoblom" wrote:

One way

=AVERAGE(IF((MOD(COLUMN(H12:AJ12),14)=8)*(H12:AJ12 <0),H12:AJ12))


needs to be entered with ctrl + shift & enter

this assumes you have the same number of columns between each value like you
have in your example


--


Regards,


Peo Sjoblom


"Danni2004" wrote in message
...
I want to find an average of three cells that are non-adjacent and will
occasionally have a zero in it.
Example:
=AVERAGE(H12,V12,AJ12)

How can I rewrite this so that the formula will not include the cell in
the
average if its value is zero?

Thanks!




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Average non-adjacent cells & exclude zeros

What are the column numbers for H12, V12, and AJ12? What does
MOD(column_number,14) return in each of those cases?

Control Shift Enter is used to enter an array formula. look up array
formula in Excel help.
--
David Biddulph

"Danni2004" wrote in message
...
Thanks Peo.
Can you clarify what the "8" is for in this formula?
Also, I've seen other posts with the Ctrl + Shift & Enter before. Can you
explain why you have to use this?


"Peo Sjoblom" wrote:

One way

=AVERAGE(IF((MOD(COLUMN(H12:AJ12),14)=8)*(H12:AJ12 <0),H12:AJ12))

needs to be entered with ctrl + shift & enter

this assumes you have the same number of columns between each value like
you
have in your example


"Danni2004" wrote in message
...
I want to find an average of three cells that are non-adjacent and will
occasionally have a zero in it.
Example:
=AVERAGE(H12,V12,AJ12)

How can I rewrite this so that the formula will not include the cell in
the
average if its value is zero?

Thanks!






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
Average Non-Adjacent Cells in Budget Spreadsheet OPB3 Excel Worksheet Functions 5 April 23rd 06 11:03 AM
Average Non-Adjacent Cells in Budget Spreadsheet OPB3 Excel Worksheet Functions 4 April 21st 06 03:36 AM
Another Average non-adjacent cells question... [email protected] Excel Discussion (Misc queries) 2 February 1st 06 06:48 PM
Average non-adjacent cells if the cell does not contain zero Cheri Excel Discussion (Misc queries) 11 August 20th 05 08:12 AM
Limit or Exclude cells in Average and Sum formula dagger Excel Worksheet Functions 3 July 7th 05 03:52 PM


All times are GMT +1. The time now is 10:52 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"