troglodyte
New Member
- Joined
- Sep 9, 2014
- Messages
- 11
Hi all, long time lurker here with a rather frustrating issue!
I have a pivot table which has information regarding a percentage share of sales for products at varying prices over several quarterly cycles (for instance, shirts selling at £5, £10, £15 on Jan 1st, Apr 1st, Jul 1st, Oct 1st etc as a percentage of our entire portfolio of sales). I will include a sample to make this easier to understand.
I am trying to graph the portfolio share of our products at a given date. Over time, the basic price of our goods change so I want to show how this change in price affects the percentage of sales.
Here's an example of what I am working with (apologies if this formats poorly):
<tbody>
</tbody>
What I want to know is which formulae would I use to return me the:
So far I have attempted combinations of the following to no avail:
I may have come close to the answer already but not written the formula in the correct order. I appreciate any help people can give me here, thank you for reading.
I have a pivot table which has information regarding a percentage share of sales for products at varying prices over several quarterly cycles (for instance, shirts selling at £5, £10, £15 on Jan 1st, Apr 1st, Jul 1st, Oct 1st etc as a percentage of our entire portfolio of sales). I will include a sample to make this easier to understand.
I am trying to graph the portfolio share of our products at a given date. Over time, the basic price of our goods change so I want to show how this change in price affects the percentage of sales.
Here's an example of what I am working with (apologies if this formats poorly):
| Item | prices | 01/01/2012 | 01/04/2012 | 01/07/2012 | 01/10/2012 | 01/01/2013 | 01/04/2013 | 01/07/2013 | 01/10/2013 | 01/01/2014 | 01/04/2014 | 01/07/2014 | |
| Socks | £5.00 | 0.8% | 0.0% | 0.1% | |||||||||
| Socks | £10.00 | ||||||||||||
| Socks | £15.00 | 0.3% | 0.4% | 0.2% | 0.1% | ||||||||
| Shirt | £80.00 | 0.3% | 0.2% | 0.9% | |||||||||
| Shirt | £100.00 | 0.7% | 0.1% | 0.1% | 0.1% | 0.4% | |||||||
| Shirt | £120.00 | 0.2% | 0.2% | 0.9% | 0.4% | ||||||||
| Shirt | £150.00 | 0.0% | 0.1% | ||||||||||
| Trousers | £150.00 | 0.1% | 0.1% | 0.1% | 0.9% | 0.6% | 0.2% | 0.1% | |||||
| Trousers | £175.00 | 0.1% | 0.2% | 0.2% | 0.3% | 0.3% | 0.2% | ||||||
| Trousers | £200.00 | 0.1% | 0.2% | 0.2% | 0.2% | 0.1% | 0.1% | 0.4% | 0.2% | 0.1% | 0.1% | 0.6% | |
| Trousers | £225.00 | 0.3% | 0.1% | 0.1% | 0.2% | 0.3% | 0.4% | 0.1% | |||||
| Trousers | £250.00 | 0.2% | 0.2% | 0.2% | 0.3% | 0.6% |
<tbody>
</tbody>
What I want to know is which formulae would I use to return me the:
- Share of the portfolio at a given date for a specific product (at its basic price), i.e. I want to know what the share of the portfolio was for trousers in January 2012 at its basic price (we can see here that it is 0.1%), what formula would I use to return this 0.1% value (and any other basic item portfolio share)?
- The basic price of an item in that quarter (if applicable). For instance the basic price of trousers in July 2012 is £175. What formula would I use to return this (and for any other item too)?
So far I have attempted combinations of the following to no avail:
- VLOOKUPS
- HLOOKUPS
- INDEX
- MATCH
- IF
I may have come close to the answer already but not written the formula in the correct order. I appreciate any help people can give me here, thank you for reading.
