Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Calculation on "only" Filtered Visible Cells
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 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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I disable "Plot visible cells only" when it is greyed out? | Charts and Charting in Excel | |||
How to Count only visible filered cells "FP" | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How can I use the "TODAY ()" Function in an "IF/THEN" calculation | Excel Worksheet Functions | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |