Adding Add'l Rows to Formulas

R

rsawh

New Member
Joined
Feb 15, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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?

Safety Tech Evaluations Pugh Matrix.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Safety Platform Pugh Matrix
2
3Problem/Situation:
4
512345
6Alternatives
7
8CriteriaBaselineColumn1SmartAppHammerTechHardHatInndexTiksColumn2TotalsRank 0
91Safe0−−0-28+Better than baseline1
102Durable0+0−040About the same0
113Weight0−−+-17−Worse than baseline-1
124Easy to assemble0+0−04SymbolsRelationshipValue
135Reliable0−−−-39Guide:
146Cost0+0+211. Before you start, collect the two sets of data.
1570+122. Insert the criteria on the left hand column.
168000 3. Insert the alternatives on the top row.
1790−-164. Work through the matrix and indicate how the criteria will affect the various alternative being considered.
18
19Totals 0-3-1  5. Review the completed matrix to make the best decision for your situation.
20Rank 132  Note: You need only to fill the white, blue and yellow cells.
21
22
23
24
25
26
27
Example
Cell Formulas
RangeFormula
K9K9=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)
L9L9=IF(K9=0,"",RANK(K9,K9:K17,0))
K10K10=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)
L10L10=IF(K10=0,"",RANK(K10,K9:K17,0))
K11K11=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)
L11L11=IF(K11=0,"",RANK(K11,K9:K17,0))
K12K12=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)
L12L12=IF(K12=0,"",RANK(K12,K9:K17,0))
K13K13=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)
L13L13=IF(K13=0,"",RANK(K13,K9:K17,0))
K14K14=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)
L14L14=IF(K14=0,"",RANK(K14,K9:K17,0))
K15K15=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)
L15L15=IF(K15=0,"",RANK(K15,K9:K17,0))
K16K16=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)
L16L16=IF(K16=0,"",RANK(K16,K9:K17,0))
K17K17=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)
L17L17=IF(K17=0,"",RANK(K17,K9:K17,0))
E19E19=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))
F19F19=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))
G19G19=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))
H19H19=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))
I19I19=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))
E20E20=IF(OR(E19=0,E19=""),"",RANK(E19,E19:I19,0))
F20F20=IF(OR(F19=0,F19=""),"",RANK(F19,E19:I19,0))
G20G20=IF(OR(G19=0,G19=""),"",RANK(G19,E19:I19,0))
H20H20=IF(OR(H19=0,H19=""),"",RANK(H19,E19:I19,0))
I20I20=IF(OR(I19=0,I19=""),"",RANK(I19,E19:I19,0))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E9:I17Cell Value="−"textNO
E9:I17Cell Value="+"textNO
E20:I20Cell Value=1textNO
L9:L17Cell Value=1textNO
K9:K17Cell Value=0textNO
E19:I19Cell Value=0textNO
Cells with Data Validation
CellAllowCriteria
E9:I17List=$N$8:$N$11
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
try this, it's a much simpler formula, i also updated the formula in k9:k17
---------------------
Book1
ABCDEFGHIJKLMNOP
1Safety Platform Pugh Matrix
2
3Problem/Situation:
4
512345
6Alternatives
7
8CriteriaBaselineColumn1SmartAppHammerTechHardHatInndexTiksColumn2TotalsRank 0
91Safe0−−0-2.0228+Better than baseline1.01
102Durable0+0−-0.00140About the same0
113Weight0−−+-1.0127−Worse than baseline-1.011
124Easy to assemble0+0−-0.0014SymbolsRelationshipValue
135Reliable0−−−-3.0339Guide:
146Cost0+0+2.0211. Before you start, collect the two sets of data.
1570+1.0122. Insert the criteria on the left hand column.
168000 3. Insert the alternatives on the top row.
1790−-1.01164. Work through the matrix and indicate how the criteria will affect the various alternative being considered.
18
19Totals -0.004-3.033-1.013  5. Review the completed matrix to make the best decision for your situation.
20Rank 132  Note: You need only to fill the white, blue and yellow cells.
Sheet1
Cell Formulas
RangeFormula
K9:K17K9=SUM(VLOOKUP(E9:I9,$N$9:$P$11,3,FALSE))
L9L9=IF(K9=0,"",RANK(K9,K9:K17,0))
L10L10=IF(K10=0,"",RANK(K10,K9:K17,0))
L11L11=IF(K11=0,"",RANK(K11,K9:K17,0))
L12L12=IF(K12=0,"",RANK(K12,K9:K17,0))
L13L13=IF(K13=0,"",RANK(K13,K9:K17,0))
L14L14=IF(K14=0,"",RANK(K14,K9:K17,0))
L15L15=IF(K15=0,"",RANK(K15,K9:K17,0))
L16L16=IF(K16=0,"",RANK(K16,K9:K17,0))
L17L17=IF(K17=0,"",RANK(K17,K9:K17,0))
E19:I19E19=IF(SUM(VLOOKUP(E9:E17,$N$9:$P$11,3,FALSE))=0,"",SUM(VLOOKUP(E9:E17,$N$9:$P$11,3,FALSE)))
E20E20=IF(OR(E19=0,E19=""),"",RANK(E19,E19:I19,0))
F20F20=IF(OR(F19=0,F19=""),"",RANK(F19,E19:I19,0))
G20G20=IF(OR(G19=0,G19=""),"",RANK(G19,E19:I19,0))
H20H20=IF(OR(H19=0,H19=""),"",RANK(H19,E19:I19,0))
I20I20=IF(OR(I19=0,I19=""),"",RANK(I19,E19:I19,0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Automating selecting cells to create average
Replies
2
Views
379
Draff
D
K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
167
XLfriend777
XLfriend777
J
  • Question
Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input
Replies
9
Views
605
jennifer7i884
J
M
  • Question
Running unique count of occurrence on table based on date
Replies
0
Views
176
Minx_Minxy
M
L
  • Solved
Help with IF Formula for my Data.
Replies
5
Views
359
Lukma
L

Forum statistics

Threads
1,215,231
Messages
6,123,754
Members
449,119
Latest member
moudenourd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top