Jason has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Ranking and average recent data
I am trying to determine a method for extracting the average price of the most recent buy or sell trades from a set of data. The information I have to determine the most recent trade is time and date. Data set below.
I would like to know the average price of the most recent 5 sells in Coffee and the most recent 9 Sells in Sugar? The problem I have is in splitting the data if the most recent trades are not the exact number I am looking for.
date/time Contract B/S Volume Price
26/06/2015 10:21 Coffee Sell 2 100
26/06/2015 10:19 Sugar Buy 3 150
26/06/2015 09:43 Sugar Buy 4 138
26/06/2015 09:13 Coffee Sell 6 99
26/06/2015 09:10 Sugar Buy 4 140
26/06/2015 09:05 Coffee Sell 3 110
26/06/2015 09:03 Coffee Buy 2 106
25/06/2015 16:30 Sugar Sell 3 152
25/06/2015 13:58 Coffee Buy 1 101
25/06/2015 09:58 Sugar Sell 2 141
25/06/2015 09:15 Coffee Buy 4 96
RE: Ranking and average recent data
Hi Jason,
Thank you for the forum question.
I copied you data and pasted to Excel.
I changed it to a dynamic table (Home tab - in the Styles group click format as table).
I clicked inside the table and added a Total row and I changed the Price column to average. The filter at the top of the table can handle the rest. Filter the Time column top 5 or top 9 and the Product column Coffee or sugar.
I have attached my workbook with the example.
I hope this is what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
filter.xlsx
