Weighted average with multiple weights

exceldashboardnoob

New Member
Joined
Oct 11, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

exceldashboardnoob

New Member
Joined
Oct 11, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi I have filled out tables as I seem to be unable to upload a screenshot:

table for products adopted, or not adopted:
category acategory b
product aproduct bproduct cproduct dproduct eproduct f
101001

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 acategory b
50%50%

weighted percentage scores for product adoption:
weighted score category aweighted 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 Acategory b
product aproduct bproduct cproduct d product eproduct f
10%30%10%20%20%10%
 

Forum statistics

Threads
1,147,477
Messages
5,741,364
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top