Posts: 9
From:Canada
Registered: Nov 2000

posted 29 November 2000 05:24 PM               

---

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.

IP: Logged

Posts: 575
From:Seymour, CT USA
Registered: Nov 98

posted 30 November 2000 09:16 AM               

---

Is the Weighted Factor fixed or variable?

Kevin

IP: Logged

Posts: 9
From:Canada
Registered: Nov 2000

posted 30 November 2000 10:59 AM               

---

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?

IP: Logged

Posts: 575
From:Seymour, CT USA
Registered: Nov 98

posted 30 November 2000 01:09 PM               

---

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

IP: Logged

Posts: 9
From:Canada
Registered: Nov 2000

posted 30 November 2000 01:34 PM               

---

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

IP: Logged

Posts: 575
From:Seymour, CT USA
Registered: Nov 98

posted 30 November 2000 05:39 PM               

---

Send it, but I think I understand your problem.

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

Kevin

IP: Logged

All times are Eastern Standard Time (USA)