Formula for Category-Dependent Weighted Grading System

[nickdecesare]

Hey all,

Been working on a little project and I seem to have hit a small snag. The workaround would take a long time, so I'm trying to avoid it but we'll see. So essentially I have two tables, a data collection and another table that has mean and standard deviation for each measurement by category. They look something like this:

---

Data Collection
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Measurement 1[/TD]
[TD]Measurement 2[/TD]
[TD]Measurement 3[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]4.8[/TD]
[TD]21[/TD]
[TD]7.07[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]4.5[/TD]
[TD]30[/TD]
[TD]6.96[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]4.8[/TD]
[TD]27[/TD]
[TD]7.66[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]4.78[/TD]
[TD]27[/TD]
[TD]7.5[/TD]
[/TR]
</tbody>[/TABLE]

---

Key


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Meas. 1 Mean[/TD]
[TD]Meas. 1 Stdev[/TD]
[TD]Meas. 2 Mean[/TD]
[TD]Meas. 2 Stdev[/TD]
[TD]Meas.3Mean[/TD]
[TD]Meas.3Stdev[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]4.77[/TD]
[TD].02[/TD]
[TD]24[/TD]
[TD]3[/TD]
[TD]7.5[/TD]
[TD].28[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]you[/TD]
[TD]get[/TD]
[TD]the[/TD]
[TD]point[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Currently, for each category I have 1 formula (if statements) for each measurement that assigns a grade by that performance on a scale of 1-5, with 3 being within 1 stdev of the mean, 2/4 being within 2 stdev (2 for lower, 4 for higher) and 1/5 for being above 2 stdevs away from the mean for that measurement. Then i have a separate formula that takes all those scores and grades the entirety of that line on a scale 1-100.

So I'm wondering if there's a single formula in which I can 1) encompass all categories in a single formula, sort of like how an array would do it, if it's category Blue, reference those means and stdevs, if black reference those respective numbers and 2) weight each measurement differently for a particular category (for instance i want to weight measurement 3 twice as much for black than the other categories in my final grade, again based on the category.

I appreciate any feedback. Either this is pretty advanced, or I'm blanking on the simple way of doing this.