Formula to assign "weights" to things based off a rating of "Low," "Medium," or "High"

SanFelippo

Board Regular
Joined
Apr 4, 2017
Messages
124
I am not sure if this is an easy task and I am just missing something but here is what I need. I need a formula that will assign a weight (with all weights adding up to 1 of course) to data in excel based off whether that data has a rating of "Low," "Mod" or "High." Naturally, low would have the lowest amount of weight assigned to it, "Mod" would have the second most weight assigned to it, and "High" would have the most weight assigned to it.

What is making this tricky is I want this formula to figure out the weighting based on how many data point there are in the data set, as it will be used on multiple things, and the total count of what's being assigned weight will change. I need the formula to be dynamic so it can adjust the weight assigned based on how many things are in the data set. For examples, see the charts below.


In the charts, in the column for formula, I would want a dynamic formula that I could put in that would calculate a weight for each item based on the Rating it was assigned as well as taking into account how many items are in the list. These weights would then of course need to add up to 1 for the entire list. Basically I would have it count how many items have been entered in the Data column and use that count in the calculation of how to distribute the weight.

I don't know if a base rating is needed, aka, if just in general we need to say something like the weights (X) in how they relate to each other are going to be Low = X, Medium = 2*X, and then High = 5*X. If that is the case then that's what I would want go with.

Any Ideas?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Rating[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg B[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg B[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg B[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg B[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg E[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg E[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg T[/TD]
[TD]High[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Rating[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg B[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg C[/TD]
[TD]High[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg E[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg E[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Do you mean something like this:

C2: =IFERROR(VLOOKUP(B2,E$3:G$5,3,),"N/A")
G3: =F3/F$6
F6: =SUMPRODUCT(COUNTIF(B2:B12,E3:E5),F3:F5)


Excel 2010
ABCDEFG
1DataRatingWeight
2FloodLow4.8%RatingRelativityWeight
3FloodLow4.8%Low14.8%
4FloodLow4.8%Mod29.5%
5FloodLow4.8%High523.8%
6Reg BMod9.5%21
7Reg BMod9.5%
8Reg BMod9.5%
9Reg BMod9.5%
10Reg EMod9.5%
11Reg EMod9.5%
12Reg THigh23.8%
13
14SUM100.0%
Sheet1
 
Last edited:
Upvote 0
Originally, that would have worked perfectly, but I have gotten one last new piece of information. Without posting another example of what the data will now look like, it's going to be confusing. So, as you can see below, we will have the Reg classification, then an Item within that Reg, and then the rating and weight we saw before.

So originally, we had it so that everything was being considered all at once and all the weights together in the entire list summed to 1. Would it be possible to have the weights assigned based off what reg classification the items have? So, what I am saying is that the weights for everything classified as "Flood" would add up to 1, and then the weights for everything classified as "Reg E" would add up to 1, and so on.

Would that be possible?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Reg[/TD]
[TD]Item[/TD]
[TD]Rating[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]MIS[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]TPA[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]CMN[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flood[/TD]
[TD]DIOP[/TD]
[TD]High[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg E[/TD]
[TD]EJIO[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg E[/TD]
[TD]FTG[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg E[/TD]
[TD]WTY[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg E[/TD]
[TD]JIO[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg B[/TD]
[TD]LKJ[/TD]
[TD]High[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg B[/TD]
[TD]SHE[/TD]
[TD]High[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg B[/TD]
[TD]UYT[/TD]
[TD]Low[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reg B[/TD]
[TD]IEB[/TD]
[TD]Mod[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
D2: =VLOOKUP(C2,F$3:G$5,2,)/SUMPRODUCT(COUNTIFS(A$2:A$13,A2,C$2:C$13,F$3:F$5),G$3:G$5)
D16: =SUMIF(A$2:A$13,A16,D$2:D$13)


Book1
ABCDEFG
1RegItemRatingWeight
2FloodMISLow11.1%RatingRelativity
3FloodTPALow11.1%Low1
4FloodCMNMod22.2%Mod2
5FloodDIOPHigh55.6%High5
6Reg EEJIOLow20.0%
7Reg EFTGLow20.0%
8Reg EWTYLow20.0%
9Reg EJIOMod40.0%
10Reg BLKJHigh38.5%
11Reg BSHEHigh38.5%
12Reg BUYTLow7.7%
13Reg BIEBMod15.4%
14
15
16Flood100.0%
17Reg E100.0%
18Reg B100.0%
Sheet1
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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
Back
Top