Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted values when one is zero
Hi,
I have 3 categories of widgets that are weighted together to acheive an overall value, item1 is 25%, item2 is 55%, item3 is 20%. I achieve my total using the following formula, which works just fine: =(item1*.25)+(item2*.55)+(item3*.20). I need some help with a formula that if 1 or 2 of the item's value is zero it doesn't screw up the weighting. If any value would be likely to be zero it would be item3. However ir is possible with any item. All help appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted values when one is zero
If one item is zero value, how should the remaining items be valued?
Is it possible to have two items at zero value? Tom wrote: Hi, I have 3 categories of widgets that are weighted together to acheive an overall value, item1 is 25%, item2 is 55%, item3 is 20%. I achieve my total using the following formula, which works just fine: =(item1*.25)+(item2*.55)+(item3*.20). I need some help with a formula that if 1 or 2 of the item's value is zero it doesn't screw up the weighting. If any value would be likely to be zero it would be item3. However ir is possible with any item. All help appreciated |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted values when one is zero
"Tom" wrote:
I have 3 categories of widgets that are weighted together to acheive an overall value, item1 is 25%, item2 is 55%, item3 is 20%. I achieve my total using the following formula, which works just fine: =(item1*.25)+(item2*.55)+(item3*.20). I need some help with a formula that if 1 or 2 of the item's value is zero it doesn't screw up the weighting. I think the answer depends on how you chose those weights in the first place. The requirement is that the sum of the weight factors is 1. For example, .25+.55+.20 = 1. This is assured if each weight factor is a fraction of a total; for example, the revenue from item1 divided by total revenue. Then if one item (i.e, the number of items sold?) is zero, it will have a natural effect on the weight factors. That is, its weight factor will be zero because its revenue is zero, but the other weights will be increased because they are a larger proportion of the total. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted values when one is zero
The weights must be normalized -- their sum must equal zero.
Try using something like... =(item1*wt1 + item2*wt2 + item3*wt3)/(wt1+wt2+wt3), where wtN is the Nth weight. On 13 Feb 2006 09:51:52 -0800, "Dave O" wrote: If one item is zero value, how should the remaining items be valued? Is it possible to have two items at zero value? Tom wrote: Hi, I have 3 categories of widgets that are weighted together to acheive an overall value, item1 is 25%, item2 is 55%, item3 is 20%. I achieve my total using the following formula, which works just fine: =(item1*.25)+(item2*.55)+(item3*.20). I need some help with a formula that if 1 or 2 of the item's value is zero it doesn't screw up the weighting. If any value would be likely to be zero it would be item3. However ir is possible with any item. All help appreciated |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted values when one is zero
The weights are determined by the "value" of the items. Item's 2 & 3
are "high value" items, therefore the need to perform better when those items are in play determined their weight. What is being measured is the amount of time to repair a Customers product. Items 2& 3 have high revenue's attached to them and are therefore more valuable and must be fixed quickly. The measurement is on the overall performance, with the high value items carrying the most weight. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted values when one is zero
On 13 Feb 2006 16:29:17 -0800, "Tom" wrote:
The weights are determined by the "value" of the items. Item's 2 & 3 are "high value" items, therefore the need to perform better when those items are in play determined their weight. What is being measured is the amount of time to repair a Customers product. Items 2& 3 have high revenue's attached to them and are therefore more valuable and must be fixed quickly. The measurement is on the overall performance, with the high value items carrying the most weight. That still does not affect the requirement that the sum of the weights should equal 1. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted values when one is zero
"Tom" wrote:
The weights are determined by the "value" of the items. Item's 2 & 3 are "high value" items, therefore the need to perform better when those items are in play determined their weight. What is being measured is the amount of time to repair a Customers product. Items 2& 3 have high revenue's attached to them and are therefore more valuable and must be fixed quickly. The measurement is on the overall performance, with the high value items carrying the most weight. I think some numerical examples would be helpful to understanding your intent. At least one or two examples each of when all 3 items are "in play" v. when only 2 items are "in play" -- perhaps an example, of when a high-revenue item is not "in play" and when a low-revenue item is not "in play". Do the arithmetic manually and show the result that you desire. Ideally, also "show your work" (what weights are used, and how the computation is done). Choose examples that exaggerate the differences. Off-hand, based on my (mis?)interpretation of the above, here is something that might work for you. Suppose A1:A3 has the cost (or revenue) for each item, B1:B3 has the number of each item "in play", and C1:C3 has the repair time for each item. D1:D3 might contain the weight factor, computed as follows (for A1, for example): A1*B1/SUMPRODUCT($A$1:$A$3,$B$1:$B$3) Then the overall performance (for A1, for example) might be C1*D1. That gives higher weight to higher-cost items -- actually to items whose total cost (count times unit cost) is higher. It also has has the effect of increasing the weight factors of each item "in play" when one or more items is not "in play" (i.e, zero). It is not clear to me if those are desired qualities of the weight system you seek. For example, dynamic weight factors can make it difficult to compare the "overall performance" between a time when all items are "in play" and when only 1 or 2 items are not "in play". That is why some numerical examples are required that clearly demonstrate your intent for varyious distinguishing circumstances. PS: Because of your special purpose, it is not necessarily the case that the sum of the weights used be 1. There might some subjective quality to your choice of weights. That is not clear to me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping a cell value constant trhoughout a list of values | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
#N/A Values : Returned by Formulas vs Entered Manually | Charts and Charting in Excel |