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]
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]