Subtotal: Min / Max Offset on Filtered Visible Cells?

Q

QTE

Hi Excel Forum,

Hope you can help me.....

Calculation on Filtered Visible Cells.

Working with Filtered Visible Cells - need calculations to recognis
"only" Filtered Visible Cells.

I need to find the value of all filtered cells (a range) within Colum
K (R11C11) based on the numeric value of filtered cells in Column
(R11C2).

I think I need to use: OFFSET property and SUBTOTAL function to perfor
arithmetical Subtraction (using MAX, MIN) to find the differenc
between two numbers, but always subtracting the lowest value from th
highest on "ONLY" Filtered Visible Cells.

Scenario for CALCULATION / FORMULA is:

The first cell row in range is Row 11
Value in Row 12, Column B (R12C2) Subtract Value in Row 11, Column
(R11C2)
Answer / Result will be the Value returned in Row 11, Column K (R11C11
from the above subtraction.

The last cell row in range is Row 250

I need to perform this calculation all the way through the range o
cells in Column B, Rows 11-250 using OFFSET? Because the subtractio
operation is performed using the higher value of the cell row below th
actual cell row value being subtracted (Row 12 Col B subtracts value i
Row 11 Col B = Row 11 Col K. That is 2 - 1 = 1):

UN-Filtered Data:

Row No. 11--12--13--14--15 .. 20..25...30..32..40
Col B-----1---2---3---4---5---10--15--20-22--30
Col K-----1---1---1---1---1----1---1---1---1---1

Result of Calculation/ Formula based on UN-Filtered Data:

You can see the calculated value for each cell in Col K is 1, as i
should be.

Row 12 Col B subtracts value in Row 11 Col B = Row 11 Col K. That is
- 1 = 1
Row 13 Col B subtracts value in Row 12 Col B = Row 12 Col K. That is
- 2 = 1
Row 14 Col B subtracts value in Row 13 Col B = Row 13 Col K. That is
- 3 = 1
Row 15 Col B subtracts value in Row 14 Col B = Row 14 Col K. That is
- 4 = 1 etc

However, when the rows are filtered, for example Rows 13, 14 and 15 ma
not meet the filter criteria and therefore, should not be visible o
included in the calculations.

So the value in each cell of Column K, will no longer be all 1’
(one’s) but rather should display a noticeable difference for each cel
value when taking account of ONLY the Visible Filtered Cells.

Result of Calculation / Formula based on Visible Filtered Data:

Row No. 11---12... 20....25...30...32...40
Col B-----1----2---10---15--20--22--30
Col K-----1----8----5----5---2---8----1

Row 12 Col B subtracts value in Row 11 Col B = Row 11 Col K. That is
- 1 = 1
Row 20 Col B subtracts value in Row 12 Col B = Row 12 Col K. That is 1
- 2 = 8
Row 25 Col B subtracts value in Row 20 Col B = Row 20 Col K. That is 1
- 10 = 5
Row 30 Col B subtracts value in Row 25 Col B = Row 25 Col K. That is 2
- 15 = 5 etc

The VBA calculation needs to perform some sort of subtraction operatio
to find the difference between two values (Subtotal: MAX, MIN)? o
Visible Filtered Cells.

Please assist with a working example.

Thank you.
QT

G

Guest

In cell B11, enter the formula:

=IF(SUBTOTAL(3,C11:J11)>0,ROW()-10,0)

In cell K11, enter the formula:

=MAX(\$B\$11:B11)-MAX(\$B10:B\$11)

Copy the formulas down to the last row of data

When you filter, column K will return the difference between the visible values in column B.

Q

QTE

Hi Debra,

Brilliant! ... Thank you so much for your help: does everything i
should.

However, could you help me out with the SUBTOTAL counta function.

It works fine, but I don't understand why the column references are
"C" and "J" - would appreciate if you could explain, please.

Thank you
QTE
*In cell B11, enter the formula:

=IF(SUBTOTAL(3,C11:J11)>0,ROW()-10,0)
[/QUOTE][/QUOTE]

D

Debra Dalgleish

You're welcome!

The C:J reference could be changed. I wanted to count the values in a
range of cells with the SUBTOTAL function, and wasn't sure if all your
columns would contain data. So, to ensure that the count would be at
least one, if the row was visible, I included all the columns in the range.
Hi Debra,

Brilliant! ... Thank you so much for your help: does everything it
should.

However, could you help me out with the SUBTOTAL counta function.

It works fine, but I don't understand why the column references are
"C" and "J" - would appreciate if you could explain, please.

Thank you
QTE
[/QUOTE]

Q

QTE

Hi Debra,

Thank you & Kind regards
QT