Formula for success... corny I know

[Roger]

I have a problem that I know the brains in here will have a solution for.

I have three variables:
1. item numbers
2. colors
3. qty of items sold

note: not all items have the same colors.

What I am trying to determine is what is my best selling color, but it's
more complicated that than.

Example: The color black sold 10000 items, but black is offered in 200
products. The color Red sold 5000 items and is offered in 50 products. So
although Black sold more individual items than red, red is more successful
because it sold more products per item number.

I currently have the data set up in a pivot table, with the Row field being
the color and the item number (Count of item number) and Qty sold (Sum of
Qty) as the data field.

So how can I rank these colors using this info.

Hope that makes sense.
thanks in advance
Roger

 

[Ashish Mathur]

Hi,

You can write a calculated formula (Pivot Table toolbar > Formulas >
Calculated formula) named run rate which will calculated as =Qty sold/count.
This will insert a new field named run rate in the pivot table

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Roger]

I'm unable to get this to work. The field that is providing the count is
simply "ItemNumber" and when I create a formula of = Quatity / ItemNumber I
get a #DiV/0! error, probably becuase the item number is a numeric field. Is
there a way to indicate that I want the "count of ItemNumber" in the formula?

 

[Ashish Mathur]

Hi,

Please mail me the file at (e-mail address removed).

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Add another column of 1's in the source data and assign a heading to it
called color code. Then us a calculated field formula in the pivot table
=quantity/color code.

Hope that helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com