Can Excel solve my problem or do I need Access / Python?

Hi everyone,

I've spent a few days looking at how to solve a problem, firstly in Excel an then Access and now I'm more confused than ever! I've been looking into this for 3 days and I'm no further forward. Please bear with me, it is quite long winded.

Its for a university project and I need to extract an answer from working on the data in order to move forward.

I can work out this number by manually working through a number of steps but I need to do it for 100 different price points on weekly data for a 3 months which is not viable manually.

These are the steps:

This is a sample of the data;

EVENT_ID ITEM_ID COUNTRY TRANSACTION_TIME PRICE NUMBER_TRANS VOLUME
107802200 5931325 GB 26/12/2012 15:45:00 670 4 0.14
107802200 5931325 GB 26/12/2012 15:45:00 880 3 0.18
107802200 5975903 GB 26/12/2012 15:45:00 3 64 2259.2
107802200 5975903 GB 26/12/2012 15:45:00 3 7 100.12

The data is then filtered to a price, ie 2.

EVENT_ID ITEM_ID COUNTRY TRANSACTION_TIME PRICE NUMBER_TRANS VOLUME
107802200 5261525 GB 26/12/2012 15:45:00 2 37 1601.52
107802200 5921132 GB 26/12/2012 15:45:00 2 46 833.5
107802076 4056023 GB 26/12/2012 12:35:00 2 7 446.44
107802078 3799249 GB 26/12/2012 13:10:00 2 55 1964.1

I then copy this visible data to a new worksheet.

I then insert a Pivot Table based on EVENT_ID and drag it in to the VALUES area so that I now know how many transactions there were at each event at the price level of 2.0 i.e.

Row Labels COUNT
107802076 1
107802078 3
107802080 1

The business only makes money when the COUNT is more than 1.

I then copy these values to another cell so I just have the values and not a formula from the Pivot Table.

I then apply a formula to show the profit earned for each EVENT_ID

https://dl.dropbox.com/u/86643329/screen.JPG

Here's an image showing the worksheet and the formula. The formula for B1 is

=100/(B2-1)

B2 is the filter price from previously, in this case 2.0.

I then total column F to show the profit at price 2.0

My problem is this; I need to do these steps for every price from 1.01 to 2.00 ie 1.01, 1.02, 1.03 to have a final Excel document showing

PRICE PROFIT
1.01 XXX
1.02 XXX
1.03 XXX

Is there a way to do this in Excel? Or do I need to try something else?

Many thanks,

Mark

Also cross posted here;
http://www.mrexcel.com/forum/excel-q...ml#post3360825