Weighting Factor for Excel

Does anyone know if Excel has a formula that calculates a given cell but gives a value back as a "weighted value" compared to the total of all cells in multiple subsets?

Signed: NotsoMathboy.

[Kevin Mader]

Is the Weighted Factor fixed or variable?

Kevin

Kevin, this would be variable.

I'm thinking that if I find the "average" percent to total for each subgroup, then I either increase or decrease each subgroup an appropriate % to reach the average...I'm I on the right track?

[Kevin Mader]

I was trying to remember back some time ago, in Excel 4.0 they had a Problem Solver and a Scenario Manager which worked with Variable data. I was thinking that if your subgroup data was placed into either engine, you might get what you are looking for. Sadly, I have forgotten 5% of the 10% of Excel I use to know.

Perhaps someone out there with good (and recent) knowledge of Excel could help out?

I am not sure why you would want to get a weighted value for subgroup data. Are you trying to make an estimation from the data?

Kevin

Not it's not an estimation of data.

I'm compiling results from a staff survey. Each of the subgroups are the "general causes" i.e. material, methods, measurements, equipment, environment, and people (cause and effect). From those general subgroups the results are then further catagorized by department. For example, a subgroup is People/Customer Service, then People/Warehouse, then equipment/warehouse, etc.
The method of grading importance was from a practice called Nominal Prioritization where the team of supervisors and senior managers each gave a grade (or priority) beside each response question per subgroup. The grade logic, applied to each response, from the survey was, the higher the number assigned to a response the more priority is was to that manager. The problem with this grading is that each subgroup had a different # of responses from the survey...therefore one subgroup had 3 responses where the highest grading for a response would be 3 and another subgroup has 14 with the highest grading per response to be 14. Since the highest priority grading in one could go as high as 14 and the other could only go to 3, I want to "weight" the subgroup with only 3 so that it had the same ranking to the total of all subgroups as the one with 14 so that I can sort all responses by the "weighted" priority # to the total of all responses.
If this sounds like a nightmare to explain I could email you the excel file for a quick peek if you want.....

[Kevin Mader]

Send it, but I think I understand your problem.

Off to the homestead, so I'll catch you in the a.m.

Kevin