0

I know there are many ways to create subtotabls that change based changes the visibility of cells - although my question might sound similar - this is NOT my question.

suppose I have a workbook with 2 columns and 5 rows e.g

.    A      B
1    Name   Val
2    A      1
3    A      3
4    B      5
5    B      2
6    A      6
7    A      
8    B      5
9    A      2

I am trying to fill cell B7 with an average of the some of the "A" named rows above it... So I filter the table based on the Name col using "A" what I want to get is the formula sum(B2,B3,B6) or (even better) sum(B2:B3,B6) i.e The sum of the cells that are visible during the creating of this formula.

I can not use subtotal because it would change its value when I change the filtering... in theory I could use subtotal and copy-paste as value but then I loose the semantics of the value.

Note that this value should remain constant regardles of future changes in row visibility (I don't even need it to change if values in A2,A3,A6 change from A to B - but I do expect it to change if B2, B3 or B6 are changed....)

I also know that if I choose the range B2:B6 (when still filtered to show only "A" named rows) and press ALT+; (windows) it will change the selection to be exactly B2:B3,B6 but this does not seem to work during the creation of a formula :(

Just to clarify- the real data is much bigger so picking the cells one by one is not an option.

Any ideas how to get the reference to the currently visible part of the currently selected range?

0

Filtering and entering in stuff like this seems like a lot of work. Why not just take the easy way out?

=IF(B2<>"","",AVERAGEIF($A$2:A2,"A",$B$2:B2))

In column C, and copy it down. Next to blank cells, it will put the average of all the "A"'s above it. You can expand it to more conditions using AVERAGEIFS, and you can change the condition from "A" to a2 if you want it to average all the observations above it for that value (so it would pick up B's, C's and so forth).

I know this doesn't answer your exact question, but your actual question can be time consuming to implement, and this is a really speedy workaround

Afterwards, you can make another column that consolidates the values - takes the value in B if it's there, otherwise the value in C. You can also just change the formula to:

=IF(B2<>"",B2,AVERAGEIF($A$2:A2,"A",$B$2:B2))

And just do that all in one column.

2
  • Although I understand how this can possibly help others, it does not fit my solution... there is no criteria column and I do not want to add one, I just want to pick some cells manually. I will wait a bit more and if I get no answer I will post an answer describing what I ended up doing.
    – epeleg
    Nov 14 '16 at 20:52
  • @epeleg You can use the following formula: =AVERAGEIF($A$2:A6, A7, $B$2:B6) and then you can just paste that into any cell you want to do manually. It will automatically update the ranges.
    – Joe
    Nov 15 '16 at 0:05
0

I was hoping to get a better solution so i waited but since there does not seem to be one here is what I ended up doing:

I filtered and selected the cells I wanted to average.

i.e. In the example provided after filtering based on the name "A" for column A I got:

.    A      B
1    Name   Val
2    A      1
3    A      3
6    A      6
7    A      
9    A      2

To be honest I am not sure what I originally did to resolve this issue, it involved selecting the cells I wanted and then copying them (forcing a marquee to appear around each of the visible cells) and then using some expression in the VBA Watches pane to get the list of addresses of all the selected cells.

however if I now try this on the example and I used the mouse to select from B2 to B6 and copying. effectively selecting B2:B3,B6 (because B4 and B5 where invisible due to the filter). I am unable to recreate my one-liner :(

That said, I did find now that I can use debug.print selection.SpecialCells(xlCellTypeVisible).address which does not even require the copy action so it is in fact probably better then my original solution.

If you intend to use this method you should be aware that there are some limits to the SpecialCells method in cases where the underlying range has more then 8192 areas - see more details about this and ways to overcome this here: https://stackoverflow.com/a/1375508/25412

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.