Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jrozendaal
 
Posts: n/a
Default find averages not including 0's for values not in contiguous rows or columns

I can find the average omitting 0 values with an array formula if
numbers are in a contiguous row or column:

=AVERAGE(IF(A2:A7<0, A2:A7,""))

BUT

what if my cells to average are A2, D2, G2, J2, M2, etc (every 3
colums) and i don't want to include 0 values?

Thanks,

jrozendaal

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default find averages not including 0's for values not in contiguous rows or columns

Try...

=AVERAGE(IF(MOD(COLUMN(A2:M2)-COLUMN(A2)+0,3)=0,IF(A2:M20,A2:M2)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range
accordingly.

Hope this helps!

In article .com,
"jrozendaal" wrote:

I can find the average omitting 0 values with an array formula if
numbers are in a contiguous row or column:

=AVERAGE(IF(A2:A7<0, A2:A7,""))

BUT

what if my cells to average are A2, D2, G2, J2, M2, etc (every 3
colums) and i don't want to include 0 values?

Thanks,

jrozendaal

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
convert columns to rows & rows to columns ROCKWARRIOR Excel Discussion (Misc queries) 2 September 23rd 05 06:31 PM
How to swap rows and columns? [email protected] Excel Discussion (Misc queries) 5 September 21st 05 08:07 AM
How to keep rows together when sorting columns? Amit Excel Worksheet Functions 1 March 28th 05 06:01 PM
interchange columns with rows Herman Excel Discussion (Misc queries) 2 December 8th 04 06:40 PM
How can I find the common names in two columns of names? hako Excel Discussion (Misc queries) 2 December 8th 04 02:59 AM


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"