Excel help - ignoring data.
Discussion
Working late tonight and have some excel data to look into.
I've 36000 entries in my excel table. Of these i have hidden quite a few in order to concentrate on the remaining entries.
However, when i try to work the average of these select entries it still seems to include the hidden data.
How can i calculate the average without excel including the hidden data?
I've 36000 entries in my excel table. Of these i have hidden quite a few in order to concentrate on the remaining entries.
However, when i try to work the average of these select entries it still seems to include the hidden data.
How can i calculate the average without excel including the hidden data?
ok....thanks for the quick reply....
i have placed filters on all the columns, and am filtering the data to give me what i am looking for. eg column A= all tests. Column b= the time taken for each test.
so when i filter column a to show me specific tests it automatically shows me the relevant times. however when i calculate the average it includes all the hidden data.
i have placed filters on all the columns, and am filtering the data to give me what i am looking for. eg column A= all tests. Column b= the time taken for each test.
so when i filter column a to show me specific tests it automatically shows me the relevant times. however when i calculate the average it includes all the hidden data.
I think you can use the subtotal function for this. =subtotal(function#,range). For example =subtotal(101,D3:D204)
When you enter the subtotal formula you'll get a pop up list which will give you all the possible function numbers. Function numbers starting at 1 (1, 2, 3 etc.) will include all of the rows, hidden or not. The functions starting at 101 (101, 102 etc.) should only work on the visible cells, ignoring any hidden ones. All of the usual functions are available, sum, average, count etc. Hope that helps.
When you enter the subtotal formula you'll get a pop up list which will give you all the possible function numbers. Function numbers starting at 1 (1, 2, 3 etc.) will include all of the rows, hidden or not. The functions starting at 101 (101, 102 etc.) should only work on the visible cells, ignoring any hidden ones. All of the usual functions are available, sum, average, count etc. Hope that helps.
Pivot table from data menu.
Select data, click pivot table
Follow the wizard.
In layout drag test type to the left column, drag the time to the main areA. Right click and change to average of time.
It will give a table with avg time, by test. Then you can also choose which tests to display from drop down box in the column header
Select data, click pivot table
Follow the wizard.
In layout drag test type to the left column, drag the time to the main areA. Right click and change to average of time.
It will give a table with avg time, by test. Then you can also choose which tests to display from drop down box in the column header
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff


