Q
QTE
Hi Excel Forum,
Hope you can help me.....
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
the actual cell row value being subtracted (Row 12 Col B subtract
value in 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
2 - 1 = 1
Row 13 Col B subtracts value in Row 12 Col B = Row 12 Col K. That is
3 - 2 = 1
Row 14 Col B subtracts value in Row 13 Col B = Row 13 Col K. That is
4 - 3 = 1
Row 15 Col B subtracts value in Row 14 Col B = Row 14 Col K. That is
5 - 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
2 - 1 = 1
Row 20 Col B subtracts value in Row 12 Col B = Row 12 Col K. That is
10 - 2 = 8
Row 25 Col B subtracts value in Row 20 Col B = Row 20 Col K. That is
15 - 10 = 5
Row 30 Col B subtracts value in Row 25 Col B = Row 25 Col K. That is
20 - 15 = 5 etc
The VBA calculation needs to perform some sort of subtraction operatio
to find the difference between to values (Subtotal: MAX, MIN)? o
Visible Filtered Cells.
Please assist with a working example.
Thank you.
QT
Hope you can help me.....
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
the actual cell row value being subtracted (Row 12 Col B subtract
value in 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
2 - 1 = 1
Row 13 Col B subtracts value in Row 12 Col B = Row 12 Col K. That is
3 - 2 = 1
Row 14 Col B subtracts value in Row 13 Col B = Row 13 Col K. That is
4 - 3 = 1
Row 15 Col B subtracts value in Row 14 Col B = Row 14 Col K. That is
5 - 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
2 - 1 = 1
Row 20 Col B subtracts value in Row 12 Col B = Row 12 Col K. That is
10 - 2 = 8
Row 25 Col B subtracts value in Row 20 Col B = Row 20 Col K. That is
15 - 10 = 5
Row 30 Col B subtracts value in Row 25 Col B = Row 25 Col K. That is
20 - 15 = 5 etc
The VBA calculation needs to perform some sort of subtraction operatio
to find the difference between to values (Subtotal: MAX, MIN)? o
Visible Filtered Cells.
Please assist with a working example.
Thank you.
QT