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

[SanFelippo]

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?

Data	Rating	Weight
Flood	Low
Flood	Low
Flood	Low
Flood	Low
Reg B	Mod
Reg B	Mod
Reg B	Mod
Reg B	Mod
Reg E	Mod
Reg E	Mod
Reg T	High

<tbody>
</tbody>

Data	Rating	Weight
Flood	Low
Flood	Low
Flood	Low
Reg B	Mod
Reg C	High
Reg E	Mod
Reg E	Mod

<tbody>
</tbody>

 

[StephenCrump]

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
	A	B	C	D	E	F	G
1	Data	Rating	Weight
2	Flood	Low	4.8%		Rating	Relativity	Weight
3	Flood	Low	4.8%		Low	1	4.8%
4	Flood	Low	4.8%		Mod	2	9.5%
5	Flood	Low	4.8%		High	5	23.8%
6	Reg B	Mod	9.5%			21
7	Reg B	Mod	9.5%
8	Reg B	Mod	9.5%
9	Reg B	Mod	9.5%
10	Reg E	Mod	9.5%
11	Reg E	Mod	9.5%
12	Reg T	High	23.8%
13
14		SUM	100.0%
Sheet1

 

[SanFelippo]

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?

Reg	Item	Rating	Weight
Flood	MIS	Low
Flood	TPA	Low
Flood	CMN	Mod
Flood	DIOP	High
Reg E	EJIO	Low
Reg E	FTG	Low
Reg E	WTY	Low
Reg E	JIO	Mod
Reg B	LKJ	High
Reg B	SHE	High
Reg B	UYT	Low
Reg B	IEB	Mod

<tbody>
</tbody>

 

[StephenCrump]

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
	A	B	C	D	E	F	G
1	Reg	Item	Rating	Weight
2	Flood	MIS	Low	11.1%		Rating	Relativity
3	Flood	TPA	Low	11.1%		Low	1
4	Flood	CMN	Mod	22.2%		Mod	2
5	Flood	DIOP	High	55.6%		High	5
6	Reg E	EJIO	Low	20.0%
7	Reg E	FTG	Low	20.0%
8	Reg E	WTY	Low	20.0%
9	Reg E	JIO	Mod	40.0%
10	Reg B	LKJ	High	38.5%
11	Reg B	SHE	High	38.5%
12	Reg B	UYT	Low	7.7%
13	Reg B	IEB	Mod	15.4%
14
15
16	Flood			100.0%
17	Reg E			100.0%
18	Reg B			100.0%
Sheet1