Weighting Factor for Excel


Ray Culver

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.
Elsmar Forum Sponsor

Ray Culver

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

One of THE Original Covers!
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?


Ray Culver

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.....
Thread starter Similar threads Forum Replies Date
A Insights on Transactional Ticket Audits such Parameters, Weighting Points, etc ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 2
O Weighting Problems (Statistics) - Weight distribution for a machine setup Capability, Accuracy and Stability - Processes, Machines, etc. 5
F Supplier Rating System - Weighting low volume suppliers vs. high volume suppliers Supplier Quality Assurance and other Supplier Issues 2
A Human Factor Validation Human Factors and Ergonomics in Engineering 4
N Must PFMEA Occurrence Factor be Changed Periodically? FMEA and Control Plans 17
P Is the second factor authentication (2FA) required for external users? Qualification and Validation (including 21 CFR Part 11) 1
A One-factor and multiple-response Minitab optimization Using Minitab Software 2
O Problem in Minitab - ERROR * Factor Voltage is highly correlated with other terms Using Minitab Software 10
E 60601-1 - Tilt testing - Tensile safety factor IEC 60601 - Medical Electrical Equipment Safety Standards Series 1
S Minitab - Factor Analysis: Label on second series of data for Biplot Using Minitab Software 4
S Is Human Factor Testing required as part of Design Control Validation? 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 3
V Using K-Factor(Tolerance Interval) Analysis for Design Verification Statistical Analysis Tools, Techniques and SPC 3
C FOS (Factor of Safety) Medical Device Other Medical Device and Orthopedic Related Topics 4
E IEC 60601-1 Tensile Load Safety Factor IEC 60601 - Medical Electrical Equipment Safety Standards Series 1
E GR&R ANOVA 3rd Factor for Fixture Nests Using Minitab Software 3
N 3 factor - 3 level experiment with DoE and Minitab Using Minitab Software 10
S Human Factor Summative Study Class III Device RA Checklist wanted Human Factors and Ergonomics in Engineering 2
M DOE: Significant factor or not Using Minitab Software 2
F Can be several measurement in a repl considered as nested factor in Minitab analysis Using Minitab Software 14
A Coverage factor in calibration certificate for a multimeter General Measurement Device and Calibration Topics 4
M Taguchi Analysis of Discrete Factor Using Minitab Software 10
N Minimum Design factor for CE Marking for machinery CE Marking (Conformité Européene) / CB Scheme 2
G Setting up a 3 Factor, 2 Level,2 response DOE in Minitab Using Minitab Software 7
Z A DOE in Minitab - 3 factors and 3 levels for each factor Using Minitab Software 12
R How do I extend Correction factor of Calibration cert to compute instrument accuracy Measurement Uncertainty (MU) 1
B DOE Question: 3 factors with 2 levels and 1 factor with 3 levels Six Sigma 2
Z Help with running 3 factor DOE factor optimization Using Minitab Software 2
P Help Setting Up and Analyzing 3 Factor 2 Level Full Factorial Design for DOE Using Minitab Software 3
C 3 Factor DOE (Design of Experiments) Template Document Control Systems, Procedures, Forms and Templates 0
P How to analyze DOE: 2 Factor, 4 Level Experiment Six Sigma 7
J DOE Analysis Experiment, 5 factors, 4 factors having 3 levels, and 1 factor having 5 Using Minitab Software 38
M Multi-factor Anova with Minitab Using Minitab Software 1
K Human Factor Assessment for IRB (Institutional Review Board) Approval? EU Medical Device Regulations 7
I Multiple Comparisons for a Random Factor Using Minitab Software 2
L DOE with 2 factors (3,4 levels) and optimized factor Using Minitab Software 12
P Multi-factor Anova with Minitab Using Minitab Software 11
R What is a Z Value/Factor and where is it most useful? Statistical Analysis Tools, Techniques and SPC 4
T 4 Factor Optimization using Minitab Using Minitab Software 7
I How GUM Uncertainty is calculated and where I locate the multiplication factor b Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 1
L Filled examples of a Factor Tree (PDCA-FTA) Analysis or a Training Document wanted Problem Solving, Root Cause Fault and Failure Analysis 2
N Test for Equal Variances - Two factor data set - Minitab Using Minitab Software 3
S Surface Finish - Conversion factor to transform Rz Jis in Rz Inspection, Prints (Drawings), Testing, Sampling and Related Topics 6
D Influence of the Coverage Factor - Worksheets based on the Average and Range Method Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 7
T Setting up a Standard Experimental Plan for Food where Factor Levels are Different Statistical Analysis Tools, Techniques and SPC 2
C DoE and ANOVA - 4 factor and 3 levels Taguchi design with 2 replicates Using Minitab Software 14
C Design of Experiments or Factor analysis using existing data Quality Tools, Improvement and Analysis 7
Y Acceptable Fatigue Safety Factor for Testing Medical Device ISO 13485:2016 - Medical Device Quality Management Systems 2
Marc TV Viewing Distance Factor After Work and Weekend Discussion Topics 9
M Control Chart for Dependent obs - One factor for each category weekly Statistical Analysis Tools, Techniques and SPC 7
C What can I treat as an input factor in Minitab? Using Minitab Software 38

Similar threads

Top Bottom