Formula/Math question (weights)

[carlidee]

Hello, this perhaps skews more on the side of a math question, but this group tends to hit the nail on the head, so I figure I could try tossing it out there! Once the structure is defined I plan to translate into a formula to auto calc. I think the solution has something to do with weighing...

Example scenario: How many of each of these categories have you eaten this week?

• Vegetables: Carrots, Cucumbers, Tomatoes, Broccoli, Squash
• Fruits: Strawberries, Peaches, Apples, Pears
• Dairy: Milk, Cheese, Yogurt

Person 1 Responses:
Vegetables: 1
Fruits: 3
Dairy: 0

Person 2 Response:
Vegetables: 3
Fruits: 1
Dairy: 0

Person 3 Responses:
Vegetables: 2
Fruits: 1
Dairy: 1

Desired outcome: I'm looking to come up with "Vegetable", "Fruit" and "Dairy" scores that gives equal weight to selections. For example right now, if one vegetable is selected then that equals 20% (1/5) for that category, if one fruit selected then that equals 25% (1/4) for that category, if one Dairy is selected then that equals 33% (1/3) for that category. I don't want someone's score for selecting one from the Vegetable category to be an inherently lower score than selecting one from Fruits just because the denominator is higher. Any thoughts??

 

[Scott Huish]

My thought is that you should divide the sum of each persons responses by the total number of items. Format as percent with whatever number of decimal places you want. I used 2.

Book2
	A	B	C	D	E	F
1	Vegetables	Carrots, Cucumbers, Tomatoes, Broccoli, Squash
2	Fruits	Strawberries, Peaches, Apples, Pears
3	Dairy	Milk, Cheese, Yogurt
4
5
6	Person 1 Responses
7	Vegetables	1	33.33%
8	Fruits	3
9	Dairy	0
10
11	Person 2 Response
12	Vegetables	3	33.33%
13	Fruits	1
14	Dairy	0
15
16	Person 3 Responses
17	Vegetables	3	41.67%
18	Fruits	1
19	Dairy	1
Sheet1

Cell Formulas
Range	Formula
C7,C17,C12	C7	=SUM(B7:B9)/12

 

[myall_blues]

This is a common thing in earth sciences (my background) when calculating mineral grades over intervals. For example, if I have 9m at 2% grade and 1m at 20% the average grade for the entire 10m is not 11% (22/2) but rather 3.8%.
The general form of the equation is (v1*w1 + v2*w2 + ... + vn*wn)/sum(w1,w2,...,wn) where v is the value to be averaged and w is the weight to be applied.
In your case the v is the persons response for each category, and the weight is the number of items in those respective categories. Multiplying these out and dividing by the sum of the weights gives a score for each person.

Book1
	A	B	C
1		Weighting factor
2	Vegetables	5
3	Fruits	4
4	Dairy	3
5
6
7	Person 1 Responses		Score
8	Vegetables	1	1.42
9	Fruits	3
10	Dairy	0
11
12	Person 2 Response
13	Vegetables	3	1.58
14	Fruits	1
15	Dairy	0
16
17	Person 3 Responses
18	Vegetables	3	1.83
19	Fruits	1
20	Dairy	1
Sheet1

Cell Formulas
Range	Formula
C8,C18,C13	C8	=(B8*$B$2+B9*$B$3+B10*$B$4)/SUM($B$2:$B$4)

 

[felixstraube]

So you want to give a score based on how many items a person has eaten in total? That is, for example if one person has eaten 3 vegetables and 1 fruit (4 in total), that person should get the same scores as a persona who has eaten 1 vegetable, 1 fruit, and 2 dairies (also 4 in total)?