| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Online Manual For Success Trading | Excel Worksheet Functions | |||
| Not a lottery success with Excel | Excel Worksheet Functions | |||
| Can Excel reveal factors for success? | Excel Discussion (Misc queries) | |||
| Determining football success.. | Excel Discussion (Misc queries) | |||
| No Success with GetSaveAs | Excel Discussion (Misc queries) | |||
Hybrid Mode
