Ranking and average recent data

Resolved · Medium Priority · Version 2010

Fri 26 Jun 2015, 17:30 Delegate Jason said...

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

Tue 30 Jun 2015, 09:26 Trainer Jens said...

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

Attached files...

filter.xlsx

Tue 7 Jul 2015: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Quickly copy a formula across sheets

Suppose you have a formula in cell Sheet1!B2, say =A1*5%, that you wish to copy to cell B2 on Sheet2, Sheet3 and Sheet4. Instead of using copy and paste, try this: (1) Select Sheet1!B2. (2) Group Sheet1 with the worksheets Sheet2, Sheet3 and Sheet4 by holding down Ctrl and clicking on the tabs of the sheets to group them. (3) Press the F2 key, then immediately press Enter to copy the formula in Sheet1!B2 across the grouped sheets.

Remember to ungroup the sheets afterwards! Right-click on any tab and choose Ungroup Sheets to do that.

View all Excel hints and tips