Weighted values when one is zero

[Tom]

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

 

[Dave O]

If one item is zero value, how should the remaining items be valued?
Is it possible to have two items at zero value?

 

[Guest]

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.

 

[Jay Somerset]

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.

 

[Tom]

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.

 

[Jay Somerset]

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.

 

[Guest]

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. D13 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.