Excel help - ignoring data.
Author
Discussion

Ray Singh

Original Poster:

3,084 posts

261 months

Tuesday 19th October 2010
quotequote all
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?

dougc

8,241 posts

296 months

Tuesday 19th October 2010
quotequote all
Is there some sort of logical rule as to which values you have hidden e.g. all value under X or all values above Y?

miniman

30,229 posts

293 months

Tuesday 19th October 2010
quotequote all
What happens if you copy the visible data and then paste "values" into a new column?

evenflow

8,889 posts

313 months

Tuesday 19th October 2010
quotequote all
I think if you copy a column (highlight all the cells with values in) when rows are hidden, then paste into a new column, it doesn't copy across the hidden stuff...

Ray Singh

Original Poster:

3,084 posts

261 months

Tuesday 19th October 2010
quotequote all
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.

Ray Singh

Original Poster:

3,084 posts

261 months

Tuesday 19th October 2010
quotequote all
Thanks for the replies. Copying and pasting does work, I just wondered if there was an easier way.

Trevelyan

729 posts

220 months

Tuesday 19th October 2010
quotequote all
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.

miniman

30,229 posts

293 months

Tuesday 19th October 2010
quotequote all
Ok I would create some new columns then use an IIF like this

IIF A1="value",B1,0

Glade

4,518 posts

254 months

Tuesday 19th October 2010
quotequote all
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

Ray Singh

Original Poster:

3,084 posts

261 months

Tuesday 19th October 2010
quotequote all
Thanks all.

Trevelyan -> Your method worked a treat.

You saved me hours of misery - many thanks. I might get my quota of 8 hrs sleep tonight.

THANK YOU !!