Formula for success... corny I know
 

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

Formula for success... corny I know
 

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" wrote in message
...
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

Formula for success... corny I know
 

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" wrote:

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" wrote in message
...
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

Formula for success... corny I know
 

Hi,

Please mail me the file at .

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Roger" wrote in message
...
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" wrote:

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" wrote in message
...
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

Formula for success... corny I know
 

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

"Roger" wrote in message
...
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" wrote:

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" wrote in message
...
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