Weighted average with multiple weights

[exceldashboardnoob]

Hi All,

I am a beginner in excel and am working with a large data set that contains products for individuals and whether they have them, or not.

Currently, I am calculating a weighted percentage for the score an individual has for each category for products. I have attached an image of a very simplified version of what I am doing currently. I.e. score for category A of products is 2/3 (the individual only has 2 out of 3 products), this category is weighted at 50% of all categorys and is adjusted to reflect that weighted in the Weighted Score table - 33% score.

What I want to be able to do is to drill down on these Weightings per category, and assign a weighting percentage to each individual product. Then, I want to be able to generate the Weighted Score for each category using the weightings for each product (rather than the overall weighting per category). This is because some products are more important than others. In the image I have attached, there is a table for weighting per product - this is the table I would like to be able to use.

Is this possible? I apologise if my post is confusing to read, I am very new to excel and learning slowly.

 

[exceldashboardnoob]

image

 

[exceldashboardnoob]

Hi I have filled out tables as I seem to be unable to upload a screenshot:

table for products adopted, or not adopted:

category a			category b
product a	product b	product c	product d	product e	product f
1	0	1	0	0	1

percentage scores for product adoption:

unweighted score category a	unweighted score category b
67%	33%

percentage weighting for each category overall:

Weighting per category
category a	category b
50%	50%

weighted percentage scores for product adoption:

weighted score category a	weighted score category b
33%	17%

I want to be able to use this table below instead as decribed above - is this possible?

weighting per product
category A			category b
product a	product b	product c	product d	product e	product f
10%	30%	10%	20%	20%	10%