RE: Subtotal: Min / Max Offset on Filtered Visible Cells?

[Debra Dalgleish]

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.
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

"QTE >" wrote:

> Hi Excel Forum,
>
> Hope you can help me.....
>
> Calculation on Filtered Visible Cells.
>
> Working with Filtered Visible Cells - need calculations to recognise
> "only" Filtered Visible Cells.
>
> I need to find the value of all filtered cells (a range) within Column
> K (R11C11) based on the numeric value of filtered cells in Column B
> (R11C2).
>
> I think I need to use: OFFSET property and SUBTOTAL function to perform
> arithmetical Subtraction (using MAX, MIN) to find the difference
> between two numbers, but always subtracting the lowest value from the
> 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 B
> (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 of
> cells in Column B, Rows 11-250 using OFFSET? Because the subtraction
> operation is performed using the higher value of the cell row below the
> actual cell row value being subtracted (Row 12 Col B subtracts 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 it
> 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 may
> not meet the filter criteria and therefore, should not be visible or
> included in the calculations.
>
> So the value in each cell of Column K, will no longer be all 1’s
> (one’s) but rather should display a noticeable difference for each cell
> 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 operation
> to find the difference between two values (Subtotal: MAX, MIN)? on
> Visible Filtered Cells.
>
> Please assist with a working example.
>
> Thank you.
> QTE
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>
>

[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.

QTE < wrote:
> 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
>
> Debra DalgleishùÛ r‰í{ n&shy;ë Hðr‰ wrote:
>
>>
>>*In cell B11, enter the formula:
>>
>>=IF(SUBTOTAL(3,C11:J11)>0,ROW()-10,0)
>>

>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>

>>>[/color] *

>>
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>