Returning the first non-empty cell from a column in a pivot table?

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

Itemprices01/01/201201/04/201201/07/201201/10/201201/01/201301/04/201301/07/201301/10/201301/01/201401/04/201401/07/2014
Socks£5.000.8%0.0%0.1%
Socks£10.00
Socks£15.000.3%0.4%0.2%0.1%
Shirt£80.000.3%0.2%0.9%
Shirt£100.000.7%0.1%0.1%0.1%0.4%
Shirt£120.000.2%0.2%0.9%0.4%
Shirt£150.000.0%0.1%
Trousers£150.000.1%0.1%0.1%0.9%0.6%0.2%0.1%
Trousers£175.000.1%0.2%0.2%0.3%0.3%0.2%
Trousers£200.000.1%0.2%0.2%0.2%0.1%0.1%0.4%0.2%0.1%0.1%0.6%
Trousers£225.000.3%0.1%0.1%0.2%0.3%0.4%0.1%
Trousers£250.000.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:

  1. 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)?
  2. 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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,277
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top