Conditional Average????

jdowski

Board Regular
Joined
Apr 21, 2002
Messages
235
Hi Excel Experts,
I am creating a worksheet where an MS Query brings in a table of records on one tab and in another tab I am using Sumproduct to summerize the table based on region and month, (Regions are the headings, Months the rows).
I have inserted blank rows after March, June, September, in order to get quarterly averages. However, the average function doesn't return an accurate number for the 4th quarter because we don't have data yet for November & December. Instead it's just taking the October results and dividing by 3.
First I thought of using some type of count statement in conjunction with the average function but since there's a formula in every cell it wasn't reflecting accuarately. Now I am thinking of some sort of if statement with the average function that would take into account months with no data and adjust accordingly???
The whole point is to have this spreadsheet be completely automated so that all one would have to do is to run the MS Query every month and the Summary tab would update automatically without end user input.

Here is one of my sumproduct formulas that I'm using:
=SUMPRODUCT((FullMonth=$A14)*(Region=$B$1),GLBal)
FullMonth & Region are dynamic named ranges in the source data table and the cell references are the corresponding row & column headings.

Thanks !!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You can do it with an array formula.
suppose you have in A1:A3 Jan,Feb and March
and in B1:B3 the ammounts you want to averaage.
In B4 array-enter this formula:

=AVERAGE(IF(B1:B3>0,B1:B3))

Remember that to array-enter a formula you have to press Ctrl+Shift+Enter instead of just "Enter"
 
Upvote 0
I tried your formula but I am getting an error. The only thing different with my actual formula is that the range I'm trying to average in non-contiguous. Here's the formula:

=average(if(B2:B4,B6:B8,B10:B12,B14:B16>0,B2:B4,B6:B8,B10:B12,B14:B16))

I'm getting the error when I try to hit Ctrl+Shift+Enter.

Thanks,

Joe
 
Upvote 0
On 2002-11-13 14:22, jdowski wrote:
I tried your formula but I am getting an error. The only thing different with my actual formula is that the range I'm trying to average in non-contiguous. Here's the formula:

=average(if(B2:B4,B6:B8,B10:B12,B14:B16>0,B2:B4,B6:B8,B10:B12,B14:B16))

I'm getting the error when I try to hit Ctrl+Shift+Enter.

Thanks,

Joe

Care to post 10 rows of your data?
 
Upvote 0
Here, the averages are cumulative, the 3 month average is for the first 3 months, the 6 month average is for the first 6, etc.
Thanks again !

MONTH BRIDGEPORT
January $203,942,584.90
February $205,027,473.64
March $204,054,768.62
3 Month Average (formula would go here)
April $203,851,757.23
May $203,851,757.23
June $200,864,781.31
6 Month Average (formula would go here)
July $197,920,784.69
August $187,484,153.46
September $188,376,291.81
9 Month Average (formula would go here)
October $190,074,836.57
November $-
December $-
12 Month Average (formula would go here)
 
Upvote 0
I am playing around with this and it seems that its the non-contiguous nature of the range that's hanging up the formula. I suppose if I had to I could put all the summary average rows at the bottom of the data but I am trying to keep the format as close as possible to an existing spreasheet that was used to do the same thing but totally manually, (end users entering each number)
I am hoping that there's a way around this.....

Joe
Woodbury, CT. USA.
 
Upvote 0
On 2002-11-13 14:36, jdowski wrote:
Here, the averages are cumulative, the 3 month average is for the first 3 months, the 6 month average is for the first 6, etc.
Thanks again !

MONTH BRIDGEPORT
January $203,942,584.90
February $205,027,473.64
March $204,054,768.62
3 Month Average (formula would go here)
April $203,851,757.23
May $203,851,757.23
June $200,864,781.31
6 Month Average (formula would go here)
July $197,920,784.69
August $187,484,153.46
September $188,376,291.81
9 Month Average (formula would go here)
October $190,074,836.57
November $-
December $-
12 Month Average (formula would go here)

Let A1:B17 house the sample you provided, including labels.

B4:

=SUM($B$2:$B$4)/MAX(1,COUNTIF($B$2:$B$4,">=0"))

B9:

=SUM($B$2:$B$8)/MAX(1,COUNTIF($B$2:$B$8,">=0"))

B13:

=SUM($B$2:$B$12)/MAX(1,COUNTIF($B$2:$B$12,">=0"))

B17:

=SUM($B$2:$B$16)/MAX(1,COUNTIF($B$2:$B$16,">=0"))

The above assumes that the range consists of positive numbers, if any.
 
Upvote 0
Ok....I see what you did, I haven't tried it yet but knowing you I'm sure it will work....my question is how and why....and, if you hadn't used that combination of functions together before, (Max, Countif, etc.) what thought process led you to come up with that.
While I appreciate all the help I get from this board, it's as important to me to develop the mindset or way of thinking to be able to come up with solutions such as what you just gave.

Joe
 
Upvote 0
On 2002-11-13 15:30, jdowski wrote:
Ok....I see what you did, I haven't tried it yet but knowing you I'm sure it will work....my question is how and why....and, if you hadn't used that combination of functions together before, (Max, Countif, etc.) what thought process led you to come up with that.
While I appreciate all the help I get from this board, it's as important to me to develop the mindset or way of thinking to be able to come up with solutions such as what you just gave.

Joe

In

=SUM(Range)/MAX(1,COUNTIF(Range,">=0"))

SUM ignores text values and empty cells; COUNTIF counts only numbers that are greater than or equal to zero (0 as number).

If there are no numbers in Range, the denomitor would become 0, causing the #DIV/0 error: MAX(1,0) computes in such cases 1 as denomitor. Note that the above formula will error out if all numbers in Range are 0's.

You can catch that too by changing the formula to...

=IF(SUM(Range),SUM(range)/COUNTIF(Range,">=0"),"")

This and earlier formula include the real 0's in the average.
 
Upvote 0
Aladin,
I am trying out your formula and the count part of it does not seem to be working properly. It is not subtracting out the previous summary rows out of the count. For instance, the 12 month average row is showing as 15 when it should be 10. The count for the 9 month average row is showing as 11 when is should be 9. Can you shed some light on this??

Joe
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top