Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Average Non-consecutive Cells

Been trying to figure this one out...

I track several parameters month/quarterly/annually in this fashion:

| Jan | Feb | Mar | Q1 | Apr | May | Jun | Q2 | Jul | Aug | Sep | Q3 |
Oct | Nov | Dec | Q4 | Annual |

In the monthly columns, I have values linked from another spreadsheet.
The Quarterly columns are the average of the preceding three months.
This part works fine with {=AVERAGE(IF(A154:C154<0,A154:C154,0))} in
Q1 for example. I use the array to eliminate a skew by future numbers,
i.e., it only averages non-zero cells.

Here's my quandary - to get the same functionality in the "Annual"
column, currently I'm placing the values from Q1,Q2, Q3 and Q4 in
contiguous cells elsewhere in the spreadsheet and using the same type
of array formula in the "Annual" cell on that block.

Is there a way to eliminate this step? The current setup just doesn't
seem elegant to me. I can't seem to figure out a formula that I can
place in "Annual" that will average the non-zero values from Q1,Q2, Q3
and Q4 since they aren't consecutive cells.

Thanks in advance...

Dave
  #2   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default Average Non-consecutive Cells

How about

=AVERAGE(D154,H154,L154,P154)

or if that doesn't work, then


=AVERAGE(IF((NOT(ISERROR(A154:P154)))*(MOD(COLUMN( A154:P154),4)=0),A154:P154))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
...
Been trying to figure this one out...

I track several parameters month/quarterly/annually in this fashion:

| Jan | Feb | Mar | Q1 | Apr | May | Jun | Q2 | Jul | Aug | Sep | Q3 |
Oct | Nov | Dec | Q4 | Annual |

In the monthly columns, I have values linked from another spreadsheet.
The Quarterly columns are the average of the preceding three months.
This part works fine with {=AVERAGE(IF(A154:C154<0,A154:C154,0))} in
Q1 for example. I use the array to eliminate a skew by future numbers,
i.e., it only averages non-zero cells.

Here's my quandary - to get the same functionality in the "Annual"
column, currently I'm placing the values from Q1,Q2, Q3 and Q4 in
contiguous cells elsewhere in the spreadsheet and using the same type
of array formula in the "Annual" cell on that block.

Is there a way to eliminate this step? The current setup just doesn't
seem elegant to me. I can't seem to figure out a formula that I can
place in "Annual" that will average the non-zero values from Q1,Q2, Q3
and Q4 since they aren't consecutive cells.

Thanks in advance...

Dave



  #3   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel
external usenet poster
 
Posts: 11,058
Default Average Non-consecutive Cells

AVERAGE() ignores blanks, but includes zeros. Change the quarter formulas to
something like:

=IF(old_quarter_formula=0,"",old_quarter_formula)

The yearly average will not be skewed by incomplete data. By the way, you
can do something similar for the quarter formulas themselves.
--
Gary''s Student - gsnu2007g


" wrote:

Been trying to figure this one out...

I track several parameters month/quarterly/annually in this fashion:

| Jan | Feb | Mar | Q1 | Apr | May | Jun | Q2 | Jul | Aug | Sep | Q3 |
Oct | Nov | Dec | Q4 | Annual |

In the monthly columns, I have values linked from another spreadsheet.
The Quarterly columns are the average of the preceding three months.
This part works fine with {=AVERAGE(IF(A154:C154<0,A154:C154,0))} in
Q1 for example. I use the array to eliminate a skew by future numbers,
i.e., it only averages non-zero cells.

Here's my quandary - to get the same functionality in the "Annual"
column, currently I'm placing the values from Q1,Q2, Q3 and Q4 in
contiguous cells elsewhere in the spreadsheet and using the same type
of array formula in the "Annual" cell on that block.

Is there a way to eliminate this step? The current setup just doesn't
seem elegant to me. I can't seem to figure out a formula that I can
place in "Annual" that will average the non-zero values from Q1,Q2, Q3
and Q4 since they aren't consecutive cells.

Thanks in advance...

Dave

  #4   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc
external usenet poster
 
Posts: 18
Default Average Non-consecutive Cells

You could use the following:
=SUM(D154,H154,L154,P154)/SUM(COUNTIF(INDIRECT({"D154","H154","L154","P154"} ),"0"))



wrote in message
...
Been trying to figure this one out...

I track several parameters month/quarterly/annually in this fashion:

| Jan | Feb | Mar | Q1 | Apr | May | Jun | Q2 | Jul | Aug | Sep | Q3 |
Oct | Nov | Dec | Q4 | Annual |

In the monthly columns, I have values linked from another spreadsheet.
The Quarterly columns are the average of the preceding three months.
This part works fine with {=AVERAGE(IF(A154:C154<0,A154:C154,0))} in
Q1 for example. I use the array to eliminate a skew by future numbers,
i.e., it only averages non-zero cells.

Here's my quandary - to get the same functionality in the "Annual"
column, currently I'm placing the values from Q1,Q2, Q3 and Q4 in
contiguous cells elsewhere in the spreadsheet and using the same type
of array formula in the "Annual" cell on that block.

Is there a way to eliminate this step? The current setup just doesn't
seem elegant to me. I can't seem to figure out a formula that I can
place in "Annual" that will average the non-zero values from Q1,Q2, Q3
and Q4 since they aren't consecutive cells.

Thanks in advance...

Dave



  #5   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Average Non-consecutive Cells

On Mar 27, 9:47 am, "GerryGerry" wrote:
You could use the following:
=SUM(D154,H154,L154,P154)/SUM(COUNTIF(INDIRECT({"D154","H154","L154","P154"} ),"0"))

wrote in message

...

Been trying to figure this one out...


I track several parameters month/quarterly/annually in this fashion:


| Jan | Feb | Mar | Q1 | Apr | May | Jun | Q2 | Jul | Aug | Sep | Q3 |
Oct | Nov | Dec | Q4 | Annual |


In the monthly columns, I have values linked from another spreadsheet.
The Quarterly columns are the average of the preceding three months.
This part works fine with {=AVERAGE(IF(A154:C154<0,A154:C154,0))} in
Q1 for example. I use the array to eliminate a skew by future numbers,
i.e., it only averages non-zero cells.


Here's my quandary - to get the same functionality in the "Annual"
column, currently I'm placing the values from Q1,Q2, Q3 and Q4 in
contiguous cells elsewhere in the spreadsheet and using the same type
of array formula in the "Annual" cell on that block.


Is there a way to eliminate this step? The current setup just doesn't
seem elegant to me. I can't seem to figure out a formula that I can
place in "Annual" that will average the non-zero values from Q1,Q2, Q3
and Q4 since they aren't consecutive cells.


Thanks in advance...


Dave


GerryGerry,

Many thanks - this works like a charm!

Dave
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
Consecutive cells pulling data from non-consective cells seed Excel Discussion (Misc queries) 2 March 6th 08 02:07 PM
How to use sum/average function on 3 consecutive values [email protected] Excel Worksheet Functions 2 November 7th 07 09:00 PM
Calculating the Average for non consecutive cells using custom for BurghRocks Excel Discussion (Misc queries) 2 October 17th 07 07:49 PM
Average, Excluding Zeros, Non-Consecutive Range Coal Miner Excel Discussion (Misc queries) 9 August 4th 05 10:21 PM
Consecutive cells Trapper via OfficeKB.com Excel Worksheet Functions 4 May 23rd 05 10:28 PM


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