Collecting sparse values

[kippertoffee]

Hello,

Sorry for the cryptic title. My problem is this - I have a large column of values, measurements made every 10 minutes. I have managed to get excel to average each hours worth of measurments in a second column for me. As i'm sure you can imagine this column is sparsely populated, only one value every 6 rows. What i would like to do is have another sheet collect these sparse values into a table. eg.

FROM

time value average
14:00 2
14:30 3 2.5
15:00 4
15:30 5 4.5
16:00 6
16:30 7 6.5

TO

time average
14:30 2.5
15:30 4.5
16:30 6.5

I can do this manually but i have a lot of data. In the past I've used python to create scripts to process my data, but it helps to keep it all in excel. Also, if possible, i'd like to stay away from VB.

Thanks a lot.

Pete

[martindwilson]

try using auto filter, select non blanks from dropdown in average column
select all, use select visible cells button, copy paste to new sheet.(visible cells button is found by right click tool bar/customise/comands/edit scroll to botom and its the symbol that looks like 4 black square boxes ,drag to toolbar)

[kippertoffee]

I'll try that now. Thanks

[Cheeky Charlie]

Or use a pivot table for your data - which will automatically average according to additional time periods you choose.

See attached...

[kippertoffee]

Wow, thanks Charlie. Pivot tables seem really powerful.

I have a couple of issues that I don't seem to be able to solve:

1 - getting the layout I need.
The attached xls has an example dataset, the pivot table I have managed to create and an example of how I would like it to look. I don't see any obvious way of getting it like that

2
The data I am working with is sound pressure levels, decibel scale. Therefore the values need to be linearised before thay are averaged. eg.

{10*log10(average(10^(0.1*RA:NGE)))}

Is it possible to ask a pivot table to linearise values, average them, then re-log the result?

Thanks again,
Pete.

[Scott Booth]

I had a similar situation where my values were sparse in a column because of how I had processed some data. My solution was to use the if and match commands to return the column of each cell. I then copy and pasted the values of cell positions into a new column and sorted that column from A to Z. In this instance I didn't need the values from cells corresponding to those column numbers; however, if one did they might use the index command.

Here is the the excel workbook containing the aforementioned solution: Dealing_with_Sparse_Columns_in_Excel(Fixed).xlsx

Hope that helps.
-Scott

P.S. Disregard the below attachment use the fixed one above