1

Product types A, B, C, D Each type has numerous products, say type A has 100 products, type B 50 etc..

product#    product type     buyer
01          A                john
02          A                
...
101         B                mary
102         B                peter
...

I want to calculate % of products sold per product type. My pivot table looks like:

total = count(product#)  ' number of 'product#' column 
sold = count(buyer)      ' number of non-empty 'buyer' column

product type    total    sold     %sold
A               100      50       50%
B               50       20       40%

I can get pivot table to show "total" and "sold" columns, but how do I calculate "%sold" column?

2
  • Your question isn't clear now. How can you get total and sold amount of products from your original data? Oct 29, 2015 at 5:04
  • Added 'buyer' column where an empty cell means it's not sold.
    – ytk
    Oct 29, 2015 at 5:11

1 Answer 1

0

You need to add a new column where you put 1 for sold and 0 for the other rows.
(can do easily with a formula: =(C2<>"")*1)

Then in table pivot table just calculate average of this column and format it as percentage.

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .