Average function with a couple criteria...

[hallingh]

Hi all. I have a spreadsheet which graphs certain data over a period of time. The user can use the built in Excel filters to filter the data by a number of criteria (the filter obviously just hides the rows that don't fulfill the criteria.)

I want the cell in X7 to do this:

Look at the range K7:K1000. Average only the cells that are not hidden (Subtotal function?) AND contain "Q1 FY10" in the corresponding cell in column I.

I've been playing around with different combos of Subtotal Sumproduct and Sumif, but can't get it to do what I need it to.

Any help would be greatly appreciated.

Thanks!

Hank

 

[barry houdini]

Hello Hank, try this "array formula"

=AVERAGE(IF(SUBTOTAL(3,OFFSET(I7,ROW(I7:I1000)-ROW(I7),0)),IF(I7:I1000="Q1 FY10",K7:K1000)))

confirmed with CTRL+SHIFT+ENTER

 

[Mike Blackman]

Hi,

This should work really...

=SUBTOTAL(1,K7:K1000)

 

[Mike Blackman]

Hi Barry, How you doing? I'll crawl back under my rock now!!!

 

[barry houdini]

Hello Mike, long time no see......

 

[hallingh]

Hmmm Barry, the formula is giving me a DIV/0 error. Maybe I'm messing something up? I switched the cell I put it in to X11, and altered the ranges in the formula accordingly. Is there something about these changes that would mess up the formula?

Also, there isn't currently a value for every cell in the range I11:I1000 or K11:K1000. I just did that so I wouldn't have to keep altering the ranges if we added more data.

Thanks a ton for the help!

Hank

 

[barry houdini]

#DIV/0! result would indicate that there are no rows that meet the 2 conditions, i.e. row is visible and column I contains the specified text. Are you sure there are rows that qualify - can you post the formula you tried?

Having blank rows in the range shouldn't cause a problem - they will be excluded because they don't meet the column I criterion.....

 

[hallingh]

Ahhhh I'm so absentminded! The actual text says "1Q FY10" in stead of "Q1 FY10" but I was scrambling to get a formula for this and didn't notice. Thanks a ton for the formula, it works great!

Hank

 

[hallingh]

OK, I have a new issue now. That formula is still working great, but I need to expand on it a bit. I'm using this formula on a number of columns, but only want it to work if there is a certain check box checked. So, I've modified the formula to this:

=IF(rL1.QualCheck = TRUE, IFERROR(AVERAGE(IF(SUBTOTAL(3,OFFSET(Focus_1_Filter!$I$11,ROW(Focus_1_Filter!$I$11:$I$1000)-ROW(Focus_1_Filter!$I$11),0)),IF(Focus_1_Filter!$I$11:$I$1000="1Q FY10",Focus_1_Filter!$K$11:$K$1000))), NA()), NA())

Basically, if the chech box is checked and the formula does not result in an error, show the average of the range, if either of those criteria are not fulfilled, then the cell is na(), so it will show a gap on my graph.

I now need to use the exact same criteria of the previous formula, but if they are all satisfied, I want it to display the average of K11:K1000 multiplied by the average of X11:X1000.

Is this possible? I would assume that it is, but I really don't have any ide ahow to write this. Any further help would be greatly appreciated. Thanks!

Hank