Average Non-duplicates Only For Filtered (non-hidden) Cells

Apr 24, 2014

how to calculate a simple average after removing duplicates. The trick is the average must work after the spreadsheet is filtered. I was able to make it work with a VBA script but the users don't like this since the numbers don't automatically update whenever they apply a different filter.

What I need: average (mean) value of (in this case age in years) based on unique ID#s that doesn't include duplicates (some ID#s are in database several time):

ID#
Age
Filter Options

11111
27
Option A

11111
27
Option A

[code]....

So if I filter by Option C, the cell with the average should return the average age of only the filtered ID#s but without the duplicates.

Correct age w/no filter: 56.8
Correct age w/option C: 59

What I have tried using: (reason for difference in cells on the equation, I condensed it in the above example so A=C and B=G)

This array works but will return hidden and non-hidden cells b/c of average(if), so it doesn't work with filter:

{=AVERAGE(IF(C3:C13C4:C14, G3:G13,FALSE))}

Also have tried the Subtotal 101 function, also with no luck: Note, a simple subtotal101 fxn will not work b/c even when I filter there will still be a few rows that are duplicate and need to be removed before averaging

=SUBTOTAL(101,(IF(C3:C13C4:C14, G3:G13,FALSE)))

View 3 Replies


ADVERTISEMENT

Avoiding Copy And Paste In Hidden / Filtered Cells

Aug 5, 2014

I have searched all over and read many solutions for selecting only visible cells and copy pasting them or formatting them by doing Ctrl + G and selecting visible cells only. However the problem is once I select visible cells only, it seems that every time I do Ctrl + C on filtered Range it only selects visible cells. I want a way to toggle this setting in Excel. I'm using Office 2007. For example: if I want to select the entire range, both hidden and visible cells within the selected table array, is there anyway to reverse or toggle the setting that causes Excel to refresh the "visible cells only" setting back to default or all cells?

It would be a much faster way than to remove all filters, select & copy entire range, and then re-apply all filters again.

View 5 Replies View Related

Exclude Hidden Cells In Average

Jul 18, 2007

I have a large data table that has autofilters on the headings.

I also have a "Data Summary" Sheet in which I would like to get a conditional average.

Here is my *working* array formula:

=AVERAGE(IF('Data Table'!$C$10:$C$65536=A2,'Data Table'!$BP$10:$BP$65536))

The problem is, it averages all cell values (conditional upon the C column's cell being equal to A2 of course), whereas I only want to average the VISIBLE cells that match the condition.

I have tried using a little VBA with:

Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim Cell As Range
Application.Volatile
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Else
Set Vis = Union(Vis, Cell)
End If
End If
Next Cell
End Function

and then trying the formula:
=AVERAGE(IF(Vis('Data Table'!$C$10:$C$65536)=A2,Vis('Data Table'!$BP$10:$BP$65536)))
but it does not work.

View 8 Replies View Related

Offset Formula Ignore Filtered / Hidden Rows?

Jun 16, 2014

I have a name which uses the formula

=OFFSET(Query!$F$1,0,0,COUNTA(Query!$F:$F)+1)

That populates a dropdown

However I want to exclude hidden / filtered rows?

Iv'e tried sumtotal etc but didnt get any results. I'm after using the rows as options.

View 1 Replies View Related

Excel 2010 :: Copy Table (listobject) INCLUDING Hidden / Filtered Rows

Nov 11, 2012

I'm trying to copy a whole bunch of tables with identical layout to a master table, so I can create a whole bunch of pivot tables that include data from ALL the various tables. The source tables MAY be filtered, and I can't work out how to copy them easily while also INCLUDING any hidden/filtered rows while at the same time leaving any filter settings on the source tables intact.

If I use something like range("Table1").Listobject.DataBodyRange.Copy then it only copies the VISIBLE rows.

But I want ALL rows to be copied to a master table.

1. I don't want to unfilter the sources tables, because users might still want the source tables to remain exactly as the user filtered them. (However, it doesn't matter if the DESTINATION list is filtered or not). I realise that I could copy the entire sheet to a temp sheet, then unfilter any tables on that sheet and THEN copy these to the master list. But wan't to know if there's a simpler way.

2. I DON'T want to use SQL to create a pivot table directly from the tables, because the tables will have further information added to them from time to time, and so if I use SQL to make a pivot directly from them, I'll have to recreate the pivot cache using that SQL query each time, which might muck up the settings in any existing pivot tables. I realise that I could use SQL to copy the data to a 'staging area', and just point the pivot table at that.

3. I can't use PowerPivot, because its not installed in this environment.

View 1 Replies View Related

Count Each Item In Filtered Range With Duplicates

Nov 20, 2006

Is it possible that, once filtered, you can count the amount a filled in cells in a column range...BUT! These cells are ID numbers for stocks, so CAN contain duplicates which represent accounts, Therefore, any duplicate will be counted as 1...

eg

12345325
12345325
435ghfdhy
5464OKff
SEDDONF4

[Code]...

As we can see here there are 14 lines of data but only 7 make up the dataset

so if X was the variable assigned to this it would = 7

Is this possible, in a loop or some sort, Would VBA hold all the Instances in its memory???

ERROR#9 OUT:

View 9 Replies View Related

Count Each Item In Filtered Range With Duplicates

Nov 20, 2006

Is it possible that, once filtered, you can count the amount a filled in cells in a column range...BUT! These cells are ID numbers for stocks, so CAN contain duplicates which represent accounts, Therefore, any duplicate will be counted as 1...

eg

12345325
12345325
435ghfdhy
5464OKff
SEDDONF4
4455ONHIG
4455ONHIG
4455ONHIG
4455ONHIG
4455ONHIG
234234
66555556
66555556
66555556

As we can see here there are 14 lines of data but only 7 make up the dataset

so if X was the variable assigned to this it would = 7

Is this possible, in a loop or some sort, Would VBA hold all the Instances in its memory?

View 9 Replies View Related

Format All Cells In All Sheets To Protection Hidden On Visible And Hidden Tabs

Feb 28, 2014

I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.

View 7 Replies View Related

Get Average Of Filtered Data?

Apr 7, 2014

I have a pivot table with multiple supervisors and emloyees, I need to be able to find the average of a supervisors department and compare it with an employee with the employee seeing everyone else's data.

I don't have access to the original data that drives this pivot table. Is there some way I can use a calculated field to contain the average performance of all the employees under this particular supervisor?

View 3 Replies View Related

Average In Filtered Data

Oct 29, 2008

I have a worksheet with exported data from a database query. I used this formula to compute the average of a column without taking the ZERO value:

View 5 Replies View Related

Average Filtered Data?

Aug 1, 2007

Is it possible to AVERAGE() the numbers that remain from a filter, and average only those numbers? Can the AVERAGE() change with the criteria that are filtered?

View 2 Replies View Related

Filtered Average Returning Wrong Value?

Jul 8, 2014

I have a table, let's call it Table1. Each column has the ability to filter but no filters are applied at the outset of this issue. I want to average a column labeled Column13. I also want to apply a filter to Column13 that states only show values over 50%. I apply the filter and in the cell I want the average calculated in I type the formula:

"=AVERAGE(101,Table1[Column13])"

The 101 (as far as I'm lead to believe) only applies the average to the rows *SHOWN*. However, the average I get back is 45.67%. This is obviously wrong as the filter states only show rows where the column is greater than 50%.

View 4 Replies View Related

Average Formula Not Working With Filtered Rows?

May 3, 2012

Filtering Date Column "A" 1st qtr 2012

Column "C" has numbers where we figure the average of the entire column
Formula: =AVERAGE(C36:C300)

When I filter or not the Date column "A", the Average is still the same.

How can I force it to only average out the rows displayed by the filter

View 4 Replies View Related

Get Average But Exclude Zeros AND Hidden Rows?

Aug 15, 2013

excluding zeros and for excluding hidden rows, but can't seem to find a way to do both at the same time, which is what I need to do.

My company has projects that come and go (and are hidden when they are gone) and at times, those projects return 0s because we do not work on them for a short period... so, when I average a column, I need to exclude both situations.

View 8 Replies View Related

Excel 2010 :: Delete Filtered Rows Without Deleting The Hidden Rows?

Sep 25, 2012

How do I delete filtered rows without deleting the hidden rows in excel 2010?

View 8 Replies View Related

Average Data Columns Ignoring Hidden Rows And Zero Values

Aug 30, 2013

I need to average the columns of data and ignore both hidden rows and zero values. I have tried writing if statements as well as the subtotal function. Both functions either ignore null values or hidden rows but not both.

The system wont let me update a sample workbook but Im wondering whether there is a formula or combo formula for this.

View 4 Replies View Related

Average Column Of Cells But Ignore Errors And Return Average Of Numbers That Are There

Jun 14, 2013

E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).

E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.

I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.

View 14 Replies View Related

Count Cells Containing Dates (greater Than 2014) Based On Filtered Cells

Jun 24, 2014

I have spreadsheet with different 100s of columns of dates with 600 rows. The first row identifies which zone the data belongs to (North, South, East, West. NE, SW, SW1, etc...)

I want to write a formula to check how many dates in each column fall in 2015 or later years; This can be accomplished by writing a countifs formula.

Where it gets complicated is once i filter on the Zones;

I want the formula to give me the desired result - count of all CELLS where the year is 2015 or greater - WITH FILTERS ON.

I stumbled upon following sumproduct formula that gives count for visible cells, however when i apply the date criteria, i get incorrect result -

=SUMPRODUCT(SUBTOTAL(3,OFFSET(IJ3:IJ999,ROW(IJ3:IJ999)-MIN(ROW(IJ1:IJ999)),,1))*(IJ3:IJ999>DATE(2014,12,31)))

View 8 Replies View Related

Pasting Into Filtered Cells Affects Other Cells Also?

Jul 24, 2013

Usually if I filter and perform some kind of operation on the cells displayed by the filter it is only these cells affected. For example, if I run a filter that shows 10 rows and I Paste a value into say, Column A, of all 10 Rows then remove the filter and look through all my rows, only the 10 cells displayed during the filter contain whatever I Pasted in.

However, sometimes when I do this many rows that were not displayed as part of the filter are affected. For example, I Paste something into my 10 filtered Rows and after removing my filter I find that whatever was Pasted into the 10 cells is now in 200 cells.

How can I be sure that any changes I make to a set of filtered cells are ONLY applied to those cells?

View 4 Replies View Related

VBA: Sum Of Filtered Cells

May 1, 2007

I have several columns on a worksheet that have an auto-filter on top of the column. The last column has numerical data.

I was wondering if there was code when:

As soon as you filter a column the last column would only indicate the data pertaining to whatever was filtered. I would like the sum of that data only. (Visible data) on a pop message box or anywhere on the sheet...

View 9 Replies View Related

Summing Cells That Having Been Filtered VBA

Dec 30, 2006

I'm using the following code to filter a particular range(it works perfectly fine). However I need to SUM Column 'L' once the data has be filtered. and place the result in the LASTS populated cell in Column L. At the moment I am selecting all the data in the column even the data that has been filtered out.

Sheets("Sheet1").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:=">16", Operator:=xlAnd

View 9 Replies View Related

Autofill Filtered Cells

Apr 7, 2008

i am trying to use a small piece of code to find the last row in an excel sheet, to copy/paste formulas and data.

Dim LastRow As Long
If WorksheetFunction. CountA( Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If

i am using this code like this:........................

if i filter only the DD the formula will find the last row being row 5, and the formula will fill all the cells untill that row, including 3 and 4. Is there any chance i can autofill only the active cells or filtered cells.. i don't know.

View 2 Replies View Related

Conditional Formatting On Filtered Cells

Apr 23, 2009

Is it possible to apply conditional formating just on visible filtered cells?
If I select (with Ctrl+Shift+Down) all visible (by filter) cells the Rules defined in Conditional formating will also apply to hidden (by filter) cells?

View 3 Replies View Related

VBA - How To Count Values In Filtered Cells

Aug 16, 2012

How do I count values in filtered cells?

After I filter for values in Columns(C), I want to count values in Columns(D). When I filter data, I noticed I can manually select the filtered cell range (i.e. Columns(D)) and at the bottom in the Status Bar it will give the correct count. If I cursor over that status bar count it will display "Number of selected cells that contain data." However, I cannot seem to figure out how to get vba to select just the filtered cells selected in that range and count them only and correctly... it instead counts all the values to the last cell in the filtered range.

For example
1) let's say I have a Range("D1:D120') and there are 20 cells containing data in that range.

2) I filter Columns(C) resulting in 10 rows and the rows are 2, 3, 20, 22, 27, 30, 37, 41, 56, 60. Of those 10 rows, there are only 2 cells containing data (let's say cell D20 and cell D37).

3) Now in reality, cells in Range("D2:D60") actually contain 10 cells with values, but since we filtered on columns(C) values... Columns(D) only shows 2 values.

...... here is my problem:
4) If I manually select the filtered range, the status bar "Count" value will correctly sum as "2." But if I code VBA or even "Record a macro code" (obviously incorreclty on my part), it will return a value of 10 for the range of D2:D60 and not on the visible filtered value which is "2."

View 6 Replies View Related

Moving Cells In A Filtered List

Nov 1, 2007

I need to identify values which are less than 0 and move those cells to an adjacent cell.

Acct Desc Amt
2100 Acct1 -10
2101 Acct2 10
The -10 cell will move one cell to the right. I have an imported list with gl accounts, descriptions and amounts and want to have the negative values moved to a new column.

View 9 Replies View Related

Use Of Index Function For Filtered Cells

Sep 23, 2006

How do i use the Index Function for Filtered Cells. The code below will ignore the hidden or filtered cells. =INDEX($A$3:$A$100,B1)

View 6 Replies View Related

Sum Odd Or Even Numbers & Visible Or Filtered Cells

Sep 4, 2007

I need to subtotal/sum a series of columns individually (one column per month across a date range) by the odd numbered visible/ filtered rows as well as (separately) by the even numbered visible/filtered rows. Preferably the totals would recalculate like a SUBTOTAL whenever the filter is altered.

I've explored using Mod and SpecialCells(xlCellTypeVisible) but I can't get a syntactically correct combination to bring back the desired results.

View 5 Replies View Related

Copying Hidden Cells

Feb 16, 2009

I am copying a range of rows (which include some hidden rows). When I paste them I see all the rows. Is there a way to retain the hidden attribute?

View 2 Replies View Related

VBA Function For Hidden Cells?

Apr 7, 2014

I am trying to make a function that will only show the value of visible cells (so I can have excel calculate a slope/offset of a filtered table. I made the function below, and it seems to work pretty well.

Option Explicit

Function Visible(x) As Variant
Application.Volatile
If x.Rows.Hidden Then
Visible = ""
ElseIf x.Columns.Hidden Then
Visible = ""
Else: Visible = x.Value
End If
End Function

However, when I try to use it in an array formula instead of with an individual cell (example, {=SUM(Visible(A1:A10))} ), it only evaluates the hidden property on the first cell. So if the first cell is hidden, all cells will be blank, and if the first cell is not hidden, all values are summed, regardless if some of the later cells are hidden.

I usually use the IF and IFERROR functions with the SUM functionto filter out values I don't want, and I assumed I could do the same with this custom function.

View 3 Replies View Related

Hidden Cells And SUBTOTAL

Feb 27, 2007

If I have two columns of data, one for mortgage rate and one for loan size, then use a data filter to display a subset of the data, how can I calculate a weighted average on just the hidden cells? Is there any sort of criteria flag that looks for hidden cells in a range? Sort of the inverse of the SUBTOTAL function.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved