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
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
