Q

#### QTE

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