Selecting data and using it for further calculation

[thesis07]

Hey!

I hope you can help me with the following problem. I have got historical intraday-data of a stock index and first of all need to summarize them by seconds:
The data looks like that:
time price
08.00.00 159
08.00.00 153
08.00.00 148
08.00.01 157
08.00.01 152
08.00.02 167
08.00.02 151
08.00.03 155
08.00.03 154
08.00.03 154

So I want to calculate the average price per second. Is there any formula to tell Excel that it should sort it by second and further calculate the average price of it. If I need to sort it by hand it would take me ages due to the huge size of the data. I hope anyone can help me=)

[FDibbins]

Hi and welcome to the foum

If I need to sort it by hand it would take me ages

excel has a built-in sorting function. You did not provide a version that you are using, so on the DATA tab, click on the SORT button

[Richard Buttrey]

Hi, and welcome to the forum.

There's no need to sort your data.

See attached

[mikeTRON]

Are you familiar with pivot tables?

You can select the entire range, then put TIME in the ROW LABELS and Price in the Values then right click in the pivot to change SUM to Average and you have an itemized avg AND a total avg at the bottom. It took me about 5 seconds to do.

Stock PivotTables.xlsx

[mehmetcik]

I would use some helper columns to do this.

I will knock up something for you.

Ok this first sheet shows you the formulae to use.

Just read Mikes Solution.

I'll stop here if that meets your requirement.

[thesis07]

Hey all togehter=)

Thanks so much for all your quick and very helpful responses. You saved me a lot of time!!!