Exclude Hidden Rows from being used within a formula

[Hughy649]

Good afternoon

My array is all data, some of it, at times, I want to hide and not be included in the calculations. I searched the internet and I believe that Subtotal should work to hide any hidden rows from being included in a formula. However, I can't figure out where to include in it the formula - front, within, the array itself?!?

I use excel 2010 and the formulas are below. Any help would be greatly appreciated.

Cheers

Andy


=SMALL(BJ108:BJ117,COUNTIF(BJ108:BJ117,0)+1) - finding the smallest # not including zero's

=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2) - finding the weighted average

 

[Aladin Akyurek]

Good afternoon

My array is all data, some of it, at times, I want to hide and not be included in the calculations. I searched the internet and I believe that Subtotal should work to hide any hidden rows from being included in a formula. However, I can't figure out where to include in it the formula - front, within, the array itself?!?

I use excel 2010 and the formulas are below. Any help would be greatly appreciated.

Cheers

Andy


=SMALL(BJ108:BJ117,COUNTIF(BJ108:BJ117,0)+1) - finding the smallest # not including zero's

=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2) - finding the weighted average

Control+shift+enter, not just enter:

=SMALL(IF(SUBTOTAL(2,OFFSET(BJ108,ROW(BJ108:BJ117)-ROW(BJ108),0,1)),
  IF(BJ108:BJ117>0,BJ108:BJ117)),2)

Assuming that the autofilter does not include columns AV, AX, and AY...

=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2)

becomes

=SUBTOTAL(9,OFFSET(BJ108,ROW(BJ108:BJ117)-ROW(BJ108),0,1))-
  AX111-AY111)/(AV109-2)

 

[Andrew Poulsom]

For you first formula you could also try:

=AGGREGATE(15,5,BJ108:BJ117,SUBTOTAL(102,BJ108:BJ117)-SUMPRODUCT(--(SUBTOTAL(109,OFFSET(BJ108,ROW(BJ108:BJ117)-ROW(BJ108),,1,1))<>0))+1)

which is a normal formula, not an array formula.

 

[Hughy649]

For you first formula you could also try:

=AGGREGATE(15,5,BJ108:BJ117,SUBTOTAL(102,BJ108:BJ117)-SUMPRODUCT(--(SUBTOTAL(109,OFFSET(BJ108,ROW(BJ108:BJ117)-ROW(BJ108),,1,1))<>0))+1)

which is a normal formula, not an array formula.

Thank you very much gentlemen - I will give them a try!

 

[Hughy649]

Thanks again gents.

I got yours to work perfectly Andrew - absolutely amazing!

This is more complicated than anticipated.

I got one other formula to work, being: =Max(F20:F60) which is now =AGGREGATE(15,5,F20:F60,SUBTOTAL(102,F20:F60))+1

However the weighted average eludes me, which is:

=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2)

As always, any assistance would be greatly appreciated.

Andy

 

[Aladin Akyurek]

Thanks again gents.

I got yours to work perfectly Andrew - absolutely amazing!

This is more complicated than anticipated.

I got one other formula to work, being: =Max(F20:F60) which is now =AGGREGATE(15,5,F20:F60,SUBTOTAL(102,F20:F60))+1

However the weighted average eludes me, which is:

=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2)

As always, any assistance would be greatly appreciated.

Andy

If you are doing manual hiding, replace 2 of SUBTOTAL with 102.

 

[Andrew Poulsom]

Try replacing SUM(BJ108:BJ117) with SUBTOTAL(109,BJ108:BJ117).

 

[Andrew Poulsom]

Also for MAX:

=SUBTOTAL(109,F20:F60)

 

[Hughy649]

Thanks Andrew

=((SUBTOTAL(109,G20:G60))-O8-P8)/(M$4-2) Worked perfectly.

And thanks for the second max formula.

Cheers

 

[Hughy649]

One last issue, being counting the number of unhidden cells in range S20:S54, with values over 0.

This looked like it should work but to no avail.

=SUMPRODUCT(SUBTOTAL(103,OFFSET(S20:S54,ROW(S20:S54)-ROW(S20),0,1)),--(S20:S54>0))

Thanks in advance.

Andy