Averageif and Hidden Cells

B

baffledmacro

New Member
Joined
Aug 15, 2008
Messages
19
Hi,

I did search about this, but couldn't find an answer; advance apologies if i've missed anything obvious.

Have a data set with multiple variables.

have a chart using the last column of variables (AR) and the results column (AS).

am using averageif to get the average of results for each variables.

e.g.

=AVERAGEIF(AR2:AR1000, "1", AS2:AS1000)

This works fine and I am happy with the results.

however, I would like to use filters on other variables (column A through to AQ), and for the average not to count the filtered data.

So far I can only see possibly using subtotal (not sure how), or writing a macro (no clue how).

Any advice hugely appreciated.

(Using Excel 2007)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Try...

=AVERAGE(IF(SUBTOTAL(3,OFFSET(AS2:AS1000,ROW(AS2:AS1000)-ROW(AS2),0,1)),IF(AR2:AR1000=1,AS2:AS1000)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Upvote 0
have a family thing all day today so only did a brief check - will go through it thoroughly tomorrow, but it appears to work perfectly - thank you so much.
 
Upvote 0
sorry - tried to edit post above but must have expired by time I finished.

one small question - if the below was e.g.

=AVERAGEIF(AR2:AR1000, "999", AS2:AS1000)

what part of the subtotal statement needs to be altered? I tried the obvious of altering the 1s in different combinations, but to no avail.
 
Upvote 0
It would be like this

=AVERAGE(IF(SUBTOTAL(3,OFFSET(AS2:AS1000,ROW(AS2:AS1000)-ROW(AS2),0,1)),IF(AR2:AR1000=999,AS2:AS1000)))

You probably don't need the quotes around 999, unless AR2:AR1000 is text formatted
 
Upvote 0
Thanks Barry

I forgot to do shift/ctrl/enter when I was trying to alter the formula, that was I think why it didnt work when I tried it.

Thanks to both of you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Average non-adjacent cells (with criteria)
2
Replies
18
Views
1K
Peter_SSs
Peter_SSs
N
  • Question
Duplicate cell to new cell using BOLDTEXT or Arial Black, font color, cell color, etc... as variables.
Replies
2
Views
333
Nvchicky
N
K
  • Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
830
arthurbr
arthurbr
N
  • Solved
ByRow & Lambda with let function issue
Replies
8
Views
637
Georgiboy
Georgiboy
K
  • Solved
Averageif with multiple cells
2 3
Replies
21
Views
1K
Alex Blakenburg
A

Forum statistics

Threads
1,215,046
Messages
6,122,849
Members
449,096
Latest member
Erald

Share this page

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