Let me preface this question by saying I am not an Excel power-user in any way, shape or form. That being said, this may have been answered previously but because of my limited Excel knowledge I just don't know what to search for.
I have a spreadsheet that I'd like to add rows to. When I do that, the formulas in Row 19 do not include those additional rows in the formula. I am unfamiliar with the formula itself so I can't recreate it or add it manually. Is there a way to do this quickly?
I have a spreadsheet that I'd like to add rows to. When I do that, the formulas in Row 19 do not include those additional rows in the formula. I am unfamiliar with the formula itself so I can't recreate it or add it manually. Is there a way to do this quickly?
| Safety Tech Evaluations Pugh Matrix.xlsx | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
| 1 | Safety Platform Pugh Matrix | |||||||||||||||||||||||
| 2 | ||||||||||||||||||||||||
| 3 | Problem/Situation: | |||||||||||||||||||||||
| 4 | ||||||||||||||||||||||||
| 5 | 1 | 2 | 3 | 4 | 5 | |||||||||||||||||||
| 6 | Alternatives | |||||||||||||||||||||||
| 7 | ||||||||||||||||||||||||
| 8 | Criteria | Baseline | Column1 | SmartApp | HammerTech | HardHat | Inndex | Tiks | Column2 | Totals | Rank | 0 | ||||||||||||
| 9 | 1 | Safe | 0 | − | − | 0 | -2 | 8 | + | Better than baseline | 1 | |||||||||||||
| 10 | 2 | Durable | 0 | + | 0 | − | 0 | 4 | 0 | About the same | 0 | |||||||||||||
| 11 | 3 | Weight | 0 | − | − | + | -1 | 7 | − | Worse than baseline | -1 | |||||||||||||
| 12 | 4 | Easy to assemble | 0 | + | 0 | − | 0 | 4 | Symbols | Relationship | Value | |||||||||||||
| 13 | 5 | Reliable | 0 | − | − | − | -3 | 9 | Guide: | |||||||||||||||
| 14 | 6 | Cost | 0 | + | 0 | + | 2 | 1 | 1. Before you start, collect the two sets of data. | |||||||||||||||
| 15 | 7 | 0 | + | 1 | 2 | 2. Insert the criteria on the left hand column. | ||||||||||||||||||
| 16 | 8 | 0 | 0 | 0 | 3. Insert the alternatives on the top row. | |||||||||||||||||||
| 17 | 9 | 0 | − | -1 | 6 | 4. Work through the matrix and indicate how the criteria will affect the various alternative being considered. | ||||||||||||||||||
| 18 | ||||||||||||||||||||||||
| 19 | Totals | 0 | -3 | -1 | 5. Review the completed matrix to make the best decision for your situation. | |||||||||||||||||||
| 20 | Rank | 1 | 3 | 2 | Note: You need only to fill the white, blue and yellow cells. | |||||||||||||||||||
| 21 | ||||||||||||||||||||||||
| 22 | ||||||||||||||||||||||||
| 23 | ||||||||||||||||||||||||
| 24 | ||||||||||||||||||||||||
| 25 | ||||||||||||||||||||||||
| 26 | ||||||||||||||||||||||||
| 27 | ||||||||||||||||||||||||
Example | ||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K9 | K9 | =VLOOKUP(E9,N8:P11,3,FALSE)+VLOOKUP(F9,N8:P11,3,FALSE)+VLOOKUP(G9,N8:P11,3,FALSE)+VLOOKUP(H9,N8:P11,3,FALSE)+VLOOKUP(I9,N8:P11,3,FALSE) |
| L9 | L9 | =IF(K9=0,"",RANK(K9,K9:K17,0)) |
| K10 | K10 | =VLOOKUP(E10,N8:P11,3,FALSE)+VLOOKUP(F10,N8:P11,3,FALSE)+VLOOKUP(G10,N8:P11,3,FALSE)+VLOOKUP(H10,N8:P11,3,FALSE)+VLOOKUP(I10,N8:P11,3,FALSE) |
| L10 | L10 | =IF(K10=0,"",RANK(K10,K9:K17,0)) |
| K11 | K11 | =VLOOKUP(E11,N8:P11,3,FALSE)+VLOOKUP(F11,N8:P11,3,FALSE)+VLOOKUP(G11,N8:P11,3,FALSE)+VLOOKUP(H11,N8:P11,3,FALSE)+VLOOKUP(I11,N8:P11,3,FALSE) |
| L11 | L11 | =IF(K11=0,"",RANK(K11,K9:K17,0)) |
| K12 | K12 | =VLOOKUP(E12,N8:P11,3,FALSE)+VLOOKUP(F12,N8:P11,3,FALSE)+VLOOKUP(G12,N8:P11,3,FALSE)+VLOOKUP(H12,N8:P11,3,FALSE)+VLOOKUP(I12,N8:P11,3,FALSE) |
| L12 | L12 | =IF(K12=0,"",RANK(K12,K9:K17,0)) |
| K13 | K13 | =VLOOKUP(E13,N8:P11,3,FALSE)+VLOOKUP(F13,N8:P11,3,FALSE)+VLOOKUP(G13,N8:P11,3,FALSE)+VLOOKUP(H13,N8:P11,3,FALSE)+VLOOKUP(I13,N8:P11,3,FALSE) |
| L13 | L13 | =IF(K13=0,"",RANK(K13,K9:K17,0)) |
| K14 | K14 | =VLOOKUP(E14,N8:P11,3,FALSE)+VLOOKUP(F14,N8:P11,3,FALSE)+VLOOKUP(G14,N8:P11,3,FALSE)+VLOOKUP(H14,N8:P11,3,FALSE)+VLOOKUP(I14,N8:P11,3,FALSE) |
| L14 | L14 | =IF(K14=0,"",RANK(K14,K9:K17,0)) |
| K15 | K15 | =VLOOKUP(E15,N8:P11,3,FALSE)+VLOOKUP(F15,N8:P11,3,FALSE)+VLOOKUP(G15,N8:P11,3,FALSE)+VLOOKUP(H15,N8:P11,3,FALSE)+VLOOKUP(I15,N8:P11,3,FALSE) |
| L15 | L15 | =IF(K15=0,"",RANK(K15,K9:K17,0)) |
| K16 | K16 | =VLOOKUP(E16,N8:P11,3,FALSE)+VLOOKUP(F16,N8:P11,3,FALSE)+VLOOKUP(G16,N8:P11,3,FALSE)+VLOOKUP(H16,N8:P11,3,FALSE)+VLOOKUP(I16,N8:P11,3,FALSE) |
| L16 | L16 | =IF(K16=0,"",RANK(K16,K9:K17,0)) |
| K17 | K17 | =VLOOKUP(E17,N8:P11,3,FALSE)+VLOOKUP(F17,N8:P11,3,FALSE)+VLOOKUP(G17,N8:P11,3,FALSE)+VLOOKUP(H17,N8:P11,3,FALSE)+VLOOKUP(I17,N8:P11,3,FALSE) |
| L17 | L17 | =IF(K17=0,"",RANK(K17,K9:K17,0)) |
| E19 | E19 | =IF(VLOOKUP(E9,N8:P11,3,FALSE)+VLOOKUP(E10,N8:P11,3,FALSE)+VLOOKUP(E11,N8:P11,3,FALSE)+VLOOKUP(E12,N8:P11,3,FALSE)+VLOOKUP(E13,N8:P11,3,FALSE)+VLOOKUP(E14,N8:P11,3,FALSE)+VLOOKUP(E15,N8:P11,3,FALSE)+VLOOKUP(E16,N8:P11,3,FALSE)+VLOOKUP(E17,N8:P11,3,FALSE)=0,"",VLOOKUP(E9,N8:P11,3,FALSE)+VLOOKUP(E10,N8:P11,3,FALSE)+VLOOKUP(E11,N8:P11,3,FALSE)+VLOOKUP(E12,N8:P11,3,FALSE)+VLOOKUP(E13,N8:P11,3,FALSE)+VLOOKUP(E14,N8:P11,3,FALSE)+VLOOKUP(E15,N8:P11,3,FALSE)+VLOOKUP(E16,N8:P11,3,FALSE)+VLOOKUP(E17,N8:P11,3,FALSE)) |
| F19 | F19 | =IF(VLOOKUP(F9,N8:P11,3,FALSE)+VLOOKUP(F10,N8:P11,3,FALSE)+VLOOKUP(F11,N8:P11,3,FALSE)+VLOOKUP(F12,N8:P11,3,FALSE)+VLOOKUP(F13,N8:P11,3,FALSE)+VLOOKUP(F14,N8:P11,3,FALSE)+VLOOKUP(F15,N8:P11,3,FALSE)+VLOOKUP(F16,N8:P11,3,FALSE)+VLOOKUP(F17,N8:P11,3,FALSE)=0,"",VLOOKUP(F9,N8:P11,3,FALSE)+VLOOKUP(F10,N8:P11,3,FALSE)+VLOOKUP(F11,N8:P11,3,FALSE)+VLOOKUP(F12,N8:P11,3,FALSE)+VLOOKUP(F13,N8:P11,3,FALSE)+VLOOKUP(F14,N8:P11,3,FALSE)+VLOOKUP(F15,N8:P11,3,FALSE)+VLOOKUP(F16,N8:P11,3,FALSE)+VLOOKUP(F17,N8:P11,3,FALSE)) |
| G19 | G19 | =IF(VLOOKUP(G9,N8:P11,3,FALSE)+VLOOKUP(G10,N8:P11,3,FALSE)+VLOOKUP(G11,N8:P11,3,FALSE)+VLOOKUP(G12,N8:P11,3,FALSE)+VLOOKUP(G13,N8:P11,3,FALSE)+VLOOKUP(G14,N8:P11,3,FALSE)+VLOOKUP(G15,N8:P11,3,FALSE)+VLOOKUP(G16,N8:P11,3,FALSE)+VLOOKUP(G17,N8:P11,3,FALSE)=0,"",VLOOKUP(G9,N8:P11,3,FALSE)+VLOOKUP(G10,N8:P11,3,FALSE)+VLOOKUP(G11,N8:P11,3,FALSE)+VLOOKUP(G12,N8:P11,3,FALSE)+VLOOKUP(G13,N8:P11,3,FALSE)+VLOOKUP(G14,N8:P11,3,FALSE)+VLOOKUP(G15,N8:P11,3,FALSE)+VLOOKUP(G16,N8:P11,3,FALSE)+VLOOKUP(G17,N8:P11,3,FALSE)) |
| H19 | H19 | =IF(VLOOKUP(H9,N8:P11,3,FALSE)+VLOOKUP(H10,N8:P11,3,FALSE)+VLOOKUP(H11,N8:P11,3,FALSE)+VLOOKUP(H12,N8:P11,3,FALSE)+VLOOKUP(H13,N8:P11,3,FALSE)+VLOOKUP(H14,N8:P11,3,FALSE)+VLOOKUP(H15,N8:P11,3,FALSE)+VLOOKUP(H16,N8:P11,3,FALSE)+VLOOKUP(H17,N8:P11,3,FALSE)=0,"",VLOOKUP(H9,N8:P11,3,FALSE)+VLOOKUP(H10,N8:P11,3,FALSE)+VLOOKUP(H11,N8:P11,3,FALSE)+VLOOKUP(H12,N8:P11,3,FALSE)+VLOOKUP(H13,N8:P11,3,FALSE)+VLOOKUP(H14,N8:P11,3,FALSE)+VLOOKUP(H15,N8:P11,3,FALSE)+VLOOKUP(H16,N8:P11,3,FALSE)+VLOOKUP(H17,N8:P11,3,FALSE)) |
| I19 | I19 | =IF(VLOOKUP(I9,N8:P11,3,FALSE)+VLOOKUP(I10,N8:P11,3,FALSE)+VLOOKUP(I11,N8:P11,3,FALSE)+VLOOKUP(I12,N8:P11,3,FALSE)+VLOOKUP(I13,N8:P11,3,FALSE)+VLOOKUP(I14,N8:P11,3,FALSE)+VLOOKUP(I15,N8:P11,3,FALSE)+VLOOKUP(I16,N8:P11,3,FALSE)+VLOOKUP(I17,N8:P11,3,FALSE)=0,"",VLOOKUP(I9,N8:P11,3,FALSE)+VLOOKUP(I10,N8:P11,3,FALSE)+VLOOKUP(I11,N8:P11,3,FALSE)+VLOOKUP(I12,N8:P11,3,FALSE)+VLOOKUP(I13,N8:P11,3,FALSE)+VLOOKUP(I14,N8:P11,3,FALSE)+VLOOKUP(I15,N8:P11,3,FALSE)+VLOOKUP(I16,N8:P11,3,FALSE)+VLOOKUP(I17,N8:P11,3,FALSE)) |
| E20 | E20 | =IF(OR(E19=0,E19=""),"",RANK(E19,E19:I19,0)) |
| F20 | F20 | =IF(OR(F19=0,F19=""),"",RANK(F19,E19:I19,0)) |
| G20 | G20 | =IF(OR(G19=0,G19=""),"",RANK(G19,E19:I19,0)) |
| H20 | H20 | =IF(OR(H19=0,H19=""),"",RANK(H19,E19:I19,0)) |
| I20 | I20 | =IF(OR(I19=0,I19=""),"",RANK(I19,E19:I19,0)) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| E9:I17 | Cell Value | ="−" | text | NO |
| E9:I17 | Cell Value | ="+" | text | NO |
| E20:I20 | Cell Value | =1 | text | NO |
| L9:L17 | Cell Value | =1 | text | NO |
| K9:K17 | Cell Value | =0 | text | NO |
| E19:I19 | Cell Value | =0 | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| E9:I17 | List | =$N$8:$N$11 |
