How to weight a number based on the inverse of it's value

[ohisofly]

In the table below I have the 2015 sales for each region, 'factor 1' and the year over year (YOY) % increase in sales for each region. I need to distribute the new objective of $1,205,000 between the new regions, based on the YOY % increase for last year and factor 1. Only twist is with factor 1 I need to weight the numbers with an inverse relationship - so the region with the lowest factor 1 value would have the highest weight for factor 1 and vice versa. Ideally I would then have two cells where I could input the weight I wanted to give to the factor 1 category, and the YOY % increase category, with a final column outputting the distributed objectives.

Thank you in advance for any input.

​

	2015 Sales	Factor 1	YOY % increase
Region A	$60,000	61	15%
Region B	$55,000	55	11%
Region C	$100,000	61	20%
Region D	$85,000	78	16%
Region E	$20,000	50	17%
Region F	$30,000	51	13%
Region G	$15,000	56	24%
Region H	$140,000	81	16%
Region I	$162,000	65	23%
Region J	$146,000	58	21%
Region K	$98,000	74	18%
Region L	$89,000	92	24%
Total obj:	$1,205,000

<tbody>
</tbody>

 

[joeu2004]

with factor 1 I need to weight the numbers with an inverse relationship - so the region with the lowest factor 1 value would have the highest weight for factor 1

Since you do not tell us the complete weighting formula, we cannot offer a dispositive solution. But perhaps the following will help.

The weights corresponding to increasing Factor 1 values is:

=C2 / SUM($C$2:$C$13)

The weights corresponding to decreasing Factor 1 values is:

=(1/C2) / SUMPRODUCT(1/$C$2:$C$13)

which can be simplified to:

=1 / SUMPRODUCT(C2/$C$2:$C$13)

for each of C2:C13.