Skip to main content
Super User

Return to Question

Updated the CSV
Source Link
Eric Marceau
Eric Marceau
  • 312
  • 3
  • 12

CSV (data; updated with input from accepted answer):

  • formulas
  • updated with input from accepted answer updated the CSV with
  • setup for next phase of "proof-of-concept" for methodology applied to structural analysis of geodesic dome
|||||||||||||||||||||||||||
||||||Circumradius|1||||||||||||||||||||
||||||Edge Length|=H2*4/SQRT(6)||||||||||||||||||||
||||||Face Height|=H3*SIN(60*PI()/180)||||||||||||||||||||
||||||Tet Height|=SQRT(6)*H3/3||||||||||||||||||||
||||||Centroid|=H4/3||||||||||||||||||||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||||
ID|Node|G_ID||||x|y|z|||||||||||||||||||
A|1|A||||=H4-H6|0|0|||||||||||||||||||
B|2|B||||=-H6|=H3/2|0|||||||||||||||||||
C|3|C||||=-H6|=-H3/2|0|||||||||||||||||||
D|4|D||||0|0|=H5|||||||||||||||||||
|||||||||||||||||||||||||||
ID|Strut|G_ID|S|E||Vx|Vy|Vz||-Vx|-Vy|-Vz||Length||Sx|Sy|Sz||Ex|Ey|Ez|||||
AB|1|AB|A|B||=(U17-Q17)/$O17|=(V17-R17)/$O17|=(W17-S17)/$O17||=-G17|=-H17|=-I17||=SQRT( (U17-Q17)^2 + (V17-R17)^2 + (W17-S17)^2 )||=VLOOKUP($D17,$A$11:$I$14,7,0)|=VLOOKUP($D17,$A$11:$I$14,8,0)|=VLOOKUP($D17,$A$11:$I$14,9,0)||=VLOOKUP($E17,$A$11:$I$14,7,0)|=VLOOKUP($E17,$A$11:$I$14,8,0)|=VLOOKUP($E17,$A$11:$I$14,9,0)|||||
BC|2|BC|B|C||=(U18-Q18)/$O18|=(V18-R18)/$O18|=(W18-S18)/$O18||=-G18|=-H18|=-I18||=SQRT( (U18-Q18)^2 + (V18-R18)^2 + (W18-S18)^2 )||=VLOOKUP($D18,$A$11:$I$14,7,0)|=VLOOKUP($D18,$A$11:$I$14,8,0)|=VLOOKUP($D18,$A$11:$I$14,9,0)||=VLOOKUP($E18,$A$11:$I$14,7,0)|=VLOOKUP($E18,$A$11:$I$14,8,0)|=VLOOKUP($E18,$A$11:$I$14,9,0)|||||
CA|3|CA|C|A||=(U19-Q19)/$O19|=(V19-R19)/$O19|=(W19-S19)/$O19||=-G19|=-H19|=-I19||=SQRT( (U19-Q19)^2 + (V19-R19)^2 + (W19-S19)^2 )||=VLOOKUP($D19,$A$11:$I$14,7,0)|=VLOOKUP($D19,$A$11:$I$14,8,0)|=VLOOKUP($D19,$A$11:$I$14,9,0)||=VLOOKUP($E19,$A$11:$I$14,7,0)|=VLOOKUP($E19,$A$11:$I$14,8,0)|=VLOOKUP($E19,$A$11:$I$14,9,0)|||||
DA|4|DA|D|A||=(U20-Q20)/$O20|=(V20-R20)/$O20|=(W20-S20)/$O20||=-G20|=-H20|=-I20||=SQRT( (U20-Q20)^2 + (V20-R20)^2 + (W20-S20)^2 )||=VLOOKUP($D20,$A$11:$I$14,7,0)|=VLOOKUP($D20,$A$11:$I$14,8,0)|=VLOOKUP($D20,$A$11:$I$14,9,0)||=VLOOKUP($E20,$A$11:$I$14,7,0)|=VLOOKUP($E20,$A$11:$I$14,8,0)|=VLOOKUP($E20,$A$11:$I$14,9,0)|||||
DB|5|DB|D|B||=(U21-Q21)/$O21|=(V21-R21)/$O21|=(W21-S21)/$O21||=-G21|=-H21|=-I21||=SQRT( (U21-Q21)^2 + (V21-R21)^2 + (W21-S21)^2 )||=VLOOKUP($D21,$A$11:$I$14,7,0)|=VLOOKUP($D21,$A$11:$I$14,8,0)|=VLOOKUP($D21,$A$11:$I$14,9,0)||=VLOOKUP($E21,$A$11:$I$14,7,0)|=VLOOKUP($E21,$A$11:$I$14,8,0)|=VLOOKUP($E21,$A$11:$I$14,9,0)|||||
DC|6|DC|D|C||=(U22-Q22)/$O22|=(V22-R22)/$O22|=(W22-S22)/$O22||=-G22|=-H22|=-I22||=SQRT( (U22-Q22)^2 + (V22-R22)^2 + (W22-S22)^2 )||=VLOOKUP($D22,$A$11:$I$14,7,0)|=VLOOKUP($D22,$A$11:$I$14,8,0)|=VLOOKUP($D22,$A$11:$I$14,9,0)||=VLOOKUP($E22,$A$11:$I$14,7,0)|=VLOOKUP($E22,$A$11:$I$14,8,0)|=VLOOKUP($E22,$A$11:$I$14,9,0)|||||
|||||||||||||||||||||||||||
||||||||||||||||||||||||Struts joined at identified nodes (contributors to force/moment equations)|||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||4|=INDEX($A$11:$A$14,MATCH(Z27,$B$11:$B$14,0),1)|"|"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||1|=INDEX($A$11:$A$14,MATCH(Z35,$B$11:$B$14,0),1)|"|"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||2|=INDEX($A$11:$A$14,MATCH(Z43,$B$11:$B$14,0),1)|"|"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||3|=INDEX($A$11:$A$14,MATCH(Z51,$B$11:$B$14,0),1)|"|"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""
|||||||||||||||||||||||||||"|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""""""),ROW($D$17:$D$22)-16)),"""""""")}""

CSV (data; updated with input from accepted answer)

|||||||||||||||||||||||||||
||||||Circumradius|1||||||||||||||||||||
||||||Edge Length|=H2*4/SQRT(6)||||||||||||||||||||
||||||Face Height|=H3*SIN(60*PI()/180)||||||||||||||||||||
||||||Tet Height|=SQRT(6)*H3/3||||||||||||||||||||
||||||Centroid|=H4/3||||||||||||||||||||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||||
ID|Node|G_ID||||x|y|z|||||||||||||||||||
A|1|A||||=H4-H6|0|0|||||||||||||||||||
B|2|B||||=-H6|=H3/2|0|||||||||||||||||||
C|3|C||||=-H6|=-H3/2|0|||||||||||||||||||
D|4|D||||0|0|=H5|||||||||||||||||||
|||||||||||||||||||||||||||
ID|Strut|G_ID|S|E||Vx|Vy|Vz||-Vx|-Vy|-Vz||Length||Sx|Sy|Sz||Ex|Ey|Ez|||||
AB|1|AB|A|B||=(U17-Q17)/$O17|=(V17-R17)/$O17|=(W17-S17)/$O17||=-G17|=-H17|=-I17||=SQRT( (U17-Q17)^2 + (V17-R17)^2 + (W17-S17)^2 )||=VLOOKUP($D17,$A$11:$I$14,7,0)|=VLOOKUP($D17,$A$11:$I$14,8,0)|=VLOOKUP($D17,$A$11:$I$14,9,0)||=VLOOKUP($E17,$A$11:$I$14,7,0)|=VLOOKUP($E17,$A$11:$I$14,8,0)|=VLOOKUP($E17,$A$11:$I$14,9,0)|||||
BC|2|BC|B|C||=(U18-Q18)/$O18|=(V18-R18)/$O18|=(W18-S18)/$O18||=-G18|=-H18|=-I18||=SQRT( (U18-Q18)^2 + (V18-R18)^2 + (W18-S18)^2 )||=VLOOKUP($D18,$A$11:$I$14,7,0)|=VLOOKUP($D18,$A$11:$I$14,8,0)|=VLOOKUP($D18,$A$11:$I$14,9,0)||=VLOOKUP($E18,$A$11:$I$14,7,0)|=VLOOKUP($E18,$A$11:$I$14,8,0)|=VLOOKUP($E18,$A$11:$I$14,9,0)|||||
CA|3|CA|C|A||=(U19-Q19)/$O19|=(V19-R19)/$O19|=(W19-S19)/$O19||=-G19|=-H19|=-I19||=SQRT( (U19-Q19)^2 + (V19-R19)^2 + (W19-S19)^2 )||=VLOOKUP($D19,$A$11:$I$14,7,0)|=VLOOKUP($D19,$A$11:$I$14,8,0)|=VLOOKUP($D19,$A$11:$I$14,9,0)||=VLOOKUP($E19,$A$11:$I$14,7,0)|=VLOOKUP($E19,$A$11:$I$14,8,0)|=VLOOKUP($E19,$A$11:$I$14,9,0)|||||
DA|4|DA|D|A||=(U20-Q20)/$O20|=(V20-R20)/$O20|=(W20-S20)/$O20||=-G20|=-H20|=-I20||=SQRT( (U20-Q20)^2 + (V20-R20)^2 + (W20-S20)^2 )||=VLOOKUP($D20,$A$11:$I$14,7,0)|=VLOOKUP($D20,$A$11:$I$14,8,0)|=VLOOKUP($D20,$A$11:$I$14,9,0)||=VLOOKUP($E20,$A$11:$I$14,7,0)|=VLOOKUP($E20,$A$11:$I$14,8,0)|=VLOOKUP($E20,$A$11:$I$14,9,0)|||||
DB|5|DB|D|B||=(U21-Q21)/$O21|=(V21-R21)/$O21|=(W21-S21)/$O21||=-G21|=-H21|=-I21||=SQRT( (U21-Q21)^2 + (V21-R21)^2 + (W21-S21)^2 )||=VLOOKUP($D21,$A$11:$I$14,7,0)|=VLOOKUP($D21,$A$11:$I$14,8,0)|=VLOOKUP($D21,$A$11:$I$14,9,0)||=VLOOKUP($E21,$A$11:$I$14,7,0)|=VLOOKUP($E21,$A$11:$I$14,8,0)|=VLOOKUP($E21,$A$11:$I$14,9,0)|||||
DC|6|DC|D|C||=(U22-Q22)/$O22|=(V22-R22)/$O22|=(W22-S22)/$O22||=-G22|=-H22|=-I22||=SQRT( (U22-Q22)^2 + (V22-R22)^2 + (W22-S22)^2 )||=VLOOKUP($D22,$A$11:$I$14,7,0)|=VLOOKUP($D22,$A$11:$I$14,8,0)|=VLOOKUP($D22,$A$11:$I$14,9,0)||=VLOOKUP($E22,$A$11:$I$14,7,0)|=VLOOKUP($E22,$A$11:$I$14,8,0)|=VLOOKUP($E22,$A$11:$I$14,9,0)|||||
|||||||||||||||||||||||||||
||||||||||||||||||||||||Struts joined at identified nodes (contributors to force/moment equations)|||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||4|=INDEX($A$11:$A$14,MATCH(Z27,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||1|=INDEX($A$11:$A$14,MATCH(Z35,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||2|=INDEX($A$11:$A$14,MATCH(Z43,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||3|=INDEX($A$11:$A$14,MATCH(Z51,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"

CSV:

  • formulas
  • updated with input from accepted answer updated the CSV with
  • setup for next phase of "proof-of-concept" for methodology applied to structural analysis of geodesic dome
|||||||||||||||||||||||||||
||||||Circumradius|1||||||||||||||||||||
||||||Edge Length|=H2*4/SQRT(6)||||||||||||||||||||
||||||Face Height|=H3*SIN(60*PI()/180)||||||||||||||||||||
||||||Tet Height|=SQRT(6)*H3/3||||||||||||||||||||
||||||Centroid|=H4/3||||||||||||||||||||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||||
ID|Node|G_ID||||x|y|z|||||||||||||||||||
A|1|A||||=H4-H6|0|0|||||||||||||||||||
B|2|B||||=-H6|=H3/2|0|||||||||||||||||||
C|3|C||||=-H6|=-H3/2|0|||||||||||||||||||
D|4|D||||0|0|=H5|||||||||||||||||||
|||||||||||||||||||||||||||
ID|Strut|G_ID|S|E||Vx|Vy|Vz||-Vx|-Vy|-Vz||Length||Sx|Sy|Sz||Ex|Ey|Ez|||||
AB|1|AB|A|B||=(U17-Q17)/$O17|=(V17-R17)/$O17|=(W17-S17)/$O17||=-G17|=-H17|=-I17||=SQRT( (U17-Q17)^2 + (V17-R17)^2 + (W17-S17)^2 )||=VLOOKUP($D17,$A$11:$I$14,7,0)|=VLOOKUP($D17,$A$11:$I$14,8,0)|=VLOOKUP($D17,$A$11:$I$14,9,0)||=VLOOKUP($E17,$A$11:$I$14,7,0)|=VLOOKUP($E17,$A$11:$I$14,8,0)|=VLOOKUP($E17,$A$11:$I$14,9,0)|||||
BC|2|BC|B|C||=(U18-Q18)/$O18|=(V18-R18)/$O18|=(W18-S18)/$O18||=-G18|=-H18|=-I18||=SQRT( (U18-Q18)^2 + (V18-R18)^2 + (W18-S18)^2 )||=VLOOKUP($D18,$A$11:$I$14,7,0)|=VLOOKUP($D18,$A$11:$I$14,8,0)|=VLOOKUP($D18,$A$11:$I$14,9,0)||=VLOOKUP($E18,$A$11:$I$14,7,0)|=VLOOKUP($E18,$A$11:$I$14,8,0)|=VLOOKUP($E18,$A$11:$I$14,9,0)|||||
CA|3|CA|C|A||=(U19-Q19)/$O19|=(V19-R19)/$O19|=(W19-S19)/$O19||=-G19|=-H19|=-I19||=SQRT( (U19-Q19)^2 + (V19-R19)^2 + (W19-S19)^2 )||=VLOOKUP($D19,$A$11:$I$14,7,0)|=VLOOKUP($D19,$A$11:$I$14,8,0)|=VLOOKUP($D19,$A$11:$I$14,9,0)||=VLOOKUP($E19,$A$11:$I$14,7,0)|=VLOOKUP($E19,$A$11:$I$14,8,0)|=VLOOKUP($E19,$A$11:$I$14,9,0)|||||
DA|4|DA|D|A||=(U20-Q20)/$O20|=(V20-R20)/$O20|=(W20-S20)/$O20||=-G20|=-H20|=-I20||=SQRT( (U20-Q20)^2 + (V20-R20)^2 + (W20-S20)^2 )||=VLOOKUP($D20,$A$11:$I$14,7,0)|=VLOOKUP($D20,$A$11:$I$14,8,0)|=VLOOKUP($D20,$A$11:$I$14,9,0)||=VLOOKUP($E20,$A$11:$I$14,7,0)|=VLOOKUP($E20,$A$11:$I$14,8,0)|=VLOOKUP($E20,$A$11:$I$14,9,0)|||||
DB|5|DB|D|B||=(U21-Q21)/$O21|=(V21-R21)/$O21|=(W21-S21)/$O21||=-G21|=-H21|=-I21||=SQRT( (U21-Q21)^2 + (V21-R21)^2 + (W21-S21)^2 )||=VLOOKUP($D21,$A$11:$I$14,7,0)|=VLOOKUP($D21,$A$11:$I$14,8,0)|=VLOOKUP($D21,$A$11:$I$14,9,0)||=VLOOKUP($E21,$A$11:$I$14,7,0)|=VLOOKUP($E21,$A$11:$I$14,8,0)|=VLOOKUP($E21,$A$11:$I$14,9,0)|||||
DC|6|DC|D|C||=(U22-Q22)/$O22|=(V22-R22)/$O22|=(W22-S22)/$O22||=-G22|=-H22|=-I22||=SQRT( (U22-Q22)^2 + (V22-R22)^2 + (W22-S22)^2 )||=VLOOKUP($D22,$A$11:$I$14,7,0)|=VLOOKUP($D22,$A$11:$I$14,8,0)|=VLOOKUP($D22,$A$11:$I$14,9,0)||=VLOOKUP($E22,$A$11:$I$14,7,0)|=VLOOKUP($E22,$A$11:$I$14,8,0)|=VLOOKUP($E22,$A$11:$I$14,9,0)|||||
|||||||||||||||||||||||||||
||||||||||||||||||||||||Struts joined at identified nodes (contributors to force/moment equations)|||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||4|=INDEX($A$11:$A$14,MATCH(Z27,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||1|=INDEX($A$11:$A$14,MATCH(Z35,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||2|=INDEX($A$11:$A$14,MATCH(Z43,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||3|=INDEX($A$11:$A$14,MATCH(Z51,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
added 4582 characters in body
Source Link
Eric Marceau
Eric Marceau
  • 312
  • 3
  • 12

CSV (datadata; updated with input from accepted answer)

|||||||||||||||||||||||||||||||||||||||||||||||||||
||||||Circumradius|1|||||||||||||||||||||||Circumradius|1||||||||||||||||||||
||||||Edge Length|=H2*4/SQRT(6)|||||||||||||||||||||||||||||||||||||
||||||Face Height|=H3*SIN(60*PI()/180)|||||||||||||||||||||||||||||||||||||
||||||Tet Height|=SQRT(6)*H3/3|||||||||||||||||3||||||||||||||||||||
||||||Centroid|=H4/3|||||||||||||||||3||||||||||||||||||||
|||||||||||||||||||||||||||||||||||||||||||||||||||
|||||||||||||||||||||||||||||||||||||||||||||||||||
|||||||||||||||||||||||||||||||||||||||||||||||||||
ID|Node|G_ID||||x|y|z||||||||||||||||ID|Node|G_ID||||x|y|z|||||||||||||||||||
A|1|A||||=H4-H6|0|0||||||||||||||||H6|0|0|||||||||||||||||||
B|2|B||||=-H6|=H3/2|0||||||||||||||||2|0|||||||||||||||||||
C|3|C||||=-H6|=-H3/2|0||||||||||||||||2|0|||||||||||||||||||
D|4|D||||0|0|=H5||||||||||||||||D|4|D||||0|0|=H5|||||||||||||||||||
|||||||||||||||||||||||||||||||||||||||||||||||||||
ID|Strut|G_ID|S|E||Vx|Vy|Vz||Length||Sx|Sy|Sz||Ex|Ey|Ez||||||ID|Strut|G_ID|S|E||Vx|Vy|Vz||-Vx|-Vy|-Vz||Length||Sx|Sy|Sz||Ex|Ey|Ez|||||
AB|1|AB|A|B||=Q17AB|1|AB|A|B||=(U17-M17|=R17Q17)/$O17|=(V17-N17|=S17R17)/$O17|=(W17-O17||=SQRTS17)/$O17||=-G17|=-H17|=-I17||=SQRT( (G17U17-Q17)^2 + (H17V17-R17)^2 + (I17W17-S17)^2 )||=VLOOKUP($D17,$A$11:$I$14,7,0)|=VLOOKUP($D17,$A$11:$I$14,8,0)|=VLOOKUP($D17,$A$11:$I$14,9,0)||=VLOOKUP($E17,$A$11:$I$14,7,0)|=VLOOKUP($E17,$A$11:$I$14,8,0)|=VLOOKUP($E17,$A$11:$I$14,9,0)|||||||||||
BC|2|BC|B|C||=Q18BC|2|BC|B|C||=(U18-M18|=R18Q18)/$O18|=(V18-N18|=S18R18)/$O18|=(W18-O18||=SQRTS18)/$O18||=-G18|=-H18|=-I18||=SQRT( (G18U18-Q18)^2 + (H18V18-R18)^2 + (I18W18-S18)^2 )||=VLOOKUP($D18,$A$11:$I$14,7,0)|=VLOOKUP($D18,$A$11:$I$14,8,0)|=VLOOKUP($D18,$A$11:$I$14,9,0)||=VLOOKUP($E18,$A$11:$I$14,7,0)|=VLOOKUP($E18,$A$11:$I$14,8,0)|=VLOOKUP($E18,$A$11:$I$14,9,0)|||||||||||
CA|3|CA|C|A||=Q19CA|3|CA|C|A||=(U19-M19|=R19Q19)/$O19|=(V19-N19|=S19R19)/$O19|=(W19-O19||=SQRTS19)/$O19||=-G19|=-H19|=-I19||=SQRT( (G19U19-Q19)^2 + (H19V19-R19)^2 + (I19W19-S19)^2 )||=VLOOKUP($D19,$A$11:$I$14,7,0)|=VLOOKUP($D19,$A$11:$I$14,8,0)|=VLOOKUP($D19,$A$11:$I$14,9,0)||=VLOOKUP($E19,$A$11:$I$14,7,0)|=VLOOKUP($E19,$A$11:$I$14,8,0)|=VLOOKUP($E19,$A$11:$I$14,9,0)|||||||||||
DA|4|DA|D|A||=Q20DA|4|DA|D|A||=(U20-M20|=R20Q20)/$O20|=(V20-N20|=S20R20)/$O20|=(W20-O20||=SQRTS20)/$O20||=-G20|=-H20|=-I20||=SQRT( (G20U20-Q20)^2 + (H20V20-R20)^2 + (I20W20-S20)^2 )||=VLOOKUP($D20,$A$11:$I$14,7,0)|=VLOOKUP($D20,$A$11:$I$14,8,0)|=VLOOKUP($D20,$A$11:$I$14,9,0)||=VLOOKUP($E20,$A$11:$I$14,7,0)|=VLOOKUP($E20,$A$11:$I$14,8,0)|=VLOOKUP($E20,$A$11:$I$14,9,0)|||||||||||
DB|5|DB|D|B||=Q21DB|5|DB|D|B||=(U21-M21|=R21Q21)/$O21|=(V21-N21|=S21R21)/$O21|=(W21-O21||=SQRTS21)/$O21||=-G21|=-H21|=-I21||=SQRT( (G21U21-Q21)^2 + (H21V21-R21)^2 + (I21W21-S21)^2 )||=VLOOKUP($D21,$A$11:$I$14,7,0)|=VLOOKUP($D21,$A$11:$I$14,8,0)|=VLOOKUP($D21,$A$11:$I$14,9,0)||=VLOOKUP($E21,$A$11:$I$14,7,0)|=VLOOKUP($E21,$A$11:$I$14,8,0)|=VLOOKUP($E21,$A$11:$I$14,9,0)||||||"=SMALL(IF(X25=B17:B22, ROW|||||
DC|6|DC|D|C||=($D$17:$E$22U22-Q22)/$O22|=(V22-2,""""R22)/$O22|=(W22-S22)"
DC|6|DC|D|C||=Q22/$O22||=-M22|=R22G22|=-N22|=S22H22|=-O22||=SQRTI22||=SQRT( (G22U22-Q22)^2 + (H22V22-R22)^2 + (I22W22-S22)^2 )||=VLOOKUP($D22,$A$11:$I$14,7,0)|=VLOOKUP($D22,$A$11:$I$14,8,0)|=VLOOKUP($D22,$A$11:$I$14,9,0)||=VLOOKUP($E22,$A$11:$I$14,7,0)|=VLOOKUP($E22,$A$11:$I$14,8,0)|=VLOOKUP($E22,$A$11:$I$14,9,0)|||||||||||
|||||||||||||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||Node||Struts||||||||||||||||||||||||Struts joined at identified nodes (contributors to force/moment equations)|||
||||||||||||||||||||||4|=INDEX|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||4|=INDEX(A11$A$11:A14$A$14,MATCH(W25Z27,B11$B$11:B14$B$14,0),1)|4|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
||||||||||||||||||||||||5|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
||||||||||||||||||||||||6|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||1|=INDEX($A$11:$A$14,MATCH(Z35,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||2|=INDEX($A$11:$A$14,MATCH(Z43,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||3|=INDEX($A$11:$A$14,MATCH(Z51,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"

CSV (data)

||||||||||||||||||||||||
||||||Circumradius|1|||||||||||||||||
||||||Edge Length|=H2*4/SQRT(6)|||||||||||||||||
||||||Face Height|=H3*SIN(60*PI()/180)|||||||||||||||||
||||||Tet Height|=SQRT(6)*H3/3|||||||||||||||||
||||||Centroid|=H4/3|||||||||||||||||
||||||||||||||||||||||||
||||||||||||||||||||||||
||||||||||||||||||||||||
ID|Node|G_ID||||x|y|z||||||||||||||||
A|1|A||||=H4-H6|0|0||||||||||||||||
B|2|B||||=-H6|=H3/2|0||||||||||||||||
C|3|C||||=-H6|=-H3/2|0||||||||||||||||
D|4|D||||0|0|=H5||||||||||||||||
||||||||||||||||||||||||
ID|Strut|G_ID|S|E||Vx|Vy|Vz||Length||Sx|Sy|Sz||Ex|Ey|Ez||||||
AB|1|AB|A|B||=Q17-M17|=R17-N17|=S17-O17||=SQRT( (G17)^2 + (H17)^2 + (I17)^2 )||=VLOOKUP($D17,$A$11:$I$14,7,0)|=VLOOKUP($D17,$A$11:$I$14,8,0)|=VLOOKUP($D17,$A$11:$I$14,9,0)||=VLOOKUP($E17,$A$11:$I$14,7,0)|=VLOOKUP($E17,$A$11:$I$14,8,0)|=VLOOKUP($E17,$A$11:$I$14,9,0)||||||
BC|2|BC|B|C||=Q18-M18|=R18-N18|=S18-O18||=SQRT( (G18)^2 + (H18)^2 + (I18)^2 )||=VLOOKUP($D18,$A$11:$I$14,7,0)|=VLOOKUP($D18,$A$11:$I$14,8,0)|=VLOOKUP($D18,$A$11:$I$14,9,0)||=VLOOKUP($E18,$A$11:$I$14,7,0)|=VLOOKUP($E18,$A$11:$I$14,8,0)|=VLOOKUP($E18,$A$11:$I$14,9,0)||||||
CA|3|CA|C|A||=Q19-M19|=R19-N19|=S19-O19||=SQRT( (G19)^2 + (H19)^2 + (I19)^2 )||=VLOOKUP($D19,$A$11:$I$14,7,0)|=VLOOKUP($D19,$A$11:$I$14,8,0)|=VLOOKUP($D19,$A$11:$I$14,9,0)||=VLOOKUP($E19,$A$11:$I$14,7,0)|=VLOOKUP($E19,$A$11:$I$14,8,0)|=VLOOKUP($E19,$A$11:$I$14,9,0)||||||
DA|4|DA|D|A||=Q20-M20|=R20-N20|=S20-O20||=SQRT( (G20)^2 + (H20)^2 + (I20)^2 )||=VLOOKUP($D20,$A$11:$I$14,7,0)|=VLOOKUP($D20,$A$11:$I$14,8,0)|=VLOOKUP($D20,$A$11:$I$14,9,0)||=VLOOKUP($E20,$A$11:$I$14,7,0)|=VLOOKUP($E20,$A$11:$I$14,8,0)|=VLOOKUP($E20,$A$11:$I$14,9,0)||||||
DB|5|DB|D|B||=Q21-M21|=R21-N21|=S21-O21||=SQRT( (G21)^2 + (H21)^2 + (I21)^2 )||=VLOOKUP($D21,$A$11:$I$14,7,0)|=VLOOKUP($D21,$A$11:$I$14,8,0)|=VLOOKUP($D21,$A$11:$I$14,9,0)||=VLOOKUP($E21,$A$11:$I$14,7,0)|=VLOOKUP($E21,$A$11:$I$14,8,0)|=VLOOKUP($E21,$A$11:$I$14,9,0)||||||"=SMALL(IF(X25=B17:B22, ROW($D$17:$E$22)-2,""""))"
DC|6|DC|D|C||=Q22-M22|=R22-N22|=S22-O22||=SQRT( (G22)^2 + (H22)^2 + (I22)^2 )||=VLOOKUP($D22,$A$11:$I$14,7,0)|=VLOOKUP($D22,$A$11:$I$14,8,0)|=VLOOKUP($D22,$A$11:$I$14,9,0)||=VLOOKUP($E22,$A$11:$I$14,7,0)|=VLOOKUP($E22,$A$11:$I$14,8,0)|=VLOOKUP($E22,$A$11:$I$14,9,0)||||||
||||||||||||||||||||||||
||||||||||||||||||||||Node||Struts
||||||||||||||||||||||4|=INDEX(A11:A14,MATCH(W25,B11:B14,0),1)|4
||||||||||||||||||||||||5
||||||||||||||||||||||||6

CSV (data; updated with input from accepted answer)

|||||||||||||||||||||||||||
||||||Circumradius|1||||||||||||||||||||
||||||Edge Length|=H2*4/SQRT(6)||||||||||||||||||||
||||||Face Height|=H3*SIN(60*PI()/180)||||||||||||||||||||
||||||Tet Height|=SQRT(6)*H3/3||||||||||||||||||||
||||||Centroid|=H4/3||||||||||||||||||||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||||
ID|Node|G_ID||||x|y|z|||||||||||||||||||
A|1|A||||=H4-H6|0|0|||||||||||||||||||
B|2|B||||=-H6|=H3/2|0|||||||||||||||||||
C|3|C||||=-H6|=-H3/2|0|||||||||||||||||||
D|4|D||||0|0|=H5|||||||||||||||||||
|||||||||||||||||||||||||||
ID|Strut|G_ID|S|E||Vx|Vy|Vz||-Vx|-Vy|-Vz||Length||Sx|Sy|Sz||Ex|Ey|Ez|||||
AB|1|AB|A|B||=(U17-Q17)/$O17|=(V17-R17)/$O17|=(W17-S17)/$O17||=-G17|=-H17|=-I17||=SQRT( (U17-Q17)^2 + (V17-R17)^2 + (W17-S17)^2 )||=VLOOKUP($D17,$A$11:$I$14,7,0)|=VLOOKUP($D17,$A$11:$I$14,8,0)|=VLOOKUP($D17,$A$11:$I$14,9,0)||=VLOOKUP($E17,$A$11:$I$14,7,0)|=VLOOKUP($E17,$A$11:$I$14,8,0)|=VLOOKUP($E17,$A$11:$I$14,9,0)|||||
BC|2|BC|B|C||=(U18-Q18)/$O18|=(V18-R18)/$O18|=(W18-S18)/$O18||=-G18|=-H18|=-I18||=SQRT( (U18-Q18)^2 + (V18-R18)^2 + (W18-S18)^2 )||=VLOOKUP($D18,$A$11:$I$14,7,0)|=VLOOKUP($D18,$A$11:$I$14,8,0)|=VLOOKUP($D18,$A$11:$I$14,9,0)||=VLOOKUP($E18,$A$11:$I$14,7,0)|=VLOOKUP($E18,$A$11:$I$14,8,0)|=VLOOKUP($E18,$A$11:$I$14,9,0)|||||
CA|3|CA|C|A||=(U19-Q19)/$O19|=(V19-R19)/$O19|=(W19-S19)/$O19||=-G19|=-H19|=-I19||=SQRT( (U19-Q19)^2 + (V19-R19)^2 + (W19-S19)^2 )||=VLOOKUP($D19,$A$11:$I$14,7,0)|=VLOOKUP($D19,$A$11:$I$14,8,0)|=VLOOKUP($D19,$A$11:$I$14,9,0)||=VLOOKUP($E19,$A$11:$I$14,7,0)|=VLOOKUP($E19,$A$11:$I$14,8,0)|=VLOOKUP($E19,$A$11:$I$14,9,0)|||||
DA|4|DA|D|A||=(U20-Q20)/$O20|=(V20-R20)/$O20|=(W20-S20)/$O20||=-G20|=-H20|=-I20||=SQRT( (U20-Q20)^2 + (V20-R20)^2 + (W20-S20)^2 )||=VLOOKUP($D20,$A$11:$I$14,7,0)|=VLOOKUP($D20,$A$11:$I$14,8,0)|=VLOOKUP($D20,$A$11:$I$14,9,0)||=VLOOKUP($E20,$A$11:$I$14,7,0)|=VLOOKUP($E20,$A$11:$I$14,8,0)|=VLOOKUP($E20,$A$11:$I$14,9,0)|||||
DB|5|DB|D|B||=(U21-Q21)/$O21|=(V21-R21)/$O21|=(W21-S21)/$O21||=-G21|=-H21|=-I21||=SQRT( (U21-Q21)^2 + (V21-R21)^2 + (W21-S21)^2 )||=VLOOKUP($D21,$A$11:$I$14,7,0)|=VLOOKUP($D21,$A$11:$I$14,8,0)|=VLOOKUP($D21,$A$11:$I$14,9,0)||=VLOOKUP($E21,$A$11:$I$14,7,0)|=VLOOKUP($E21,$A$11:$I$14,8,0)|=VLOOKUP($E21,$A$11:$I$14,9,0)|||||
DC|6|DC|D|C||=(U22-Q22)/$O22|=(V22-R22)/$O22|=(W22-S22)/$O22||=-G22|=-H22|=-I22||=SQRT( (U22-Q22)^2 + (V22-R22)^2 + (W22-S22)^2 )||=VLOOKUP($D22,$A$11:$I$14,7,0)|=VLOOKUP($D22,$A$11:$I$14,8,0)|=VLOOKUP($D22,$A$11:$I$14,9,0)||=VLOOKUP($E22,$A$11:$I$14,7,0)|=VLOOKUP($E22,$A$11:$I$14,8,0)|=VLOOKUP($E22,$A$11:$I$14,9,0)|||||
|||||||||||||||||||||||||||
||||||||||||||||||||||||Struts joined at identified nodes (contributors to force/moment equations)|||
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||4|=INDEX($A$11:$A$14,MATCH(Z27,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA27=$D$17:$D$22)+(AA27=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||1|=INDEX($A$11:$A$14,MATCH(Z35,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA35=$D$17:$D$22)+(AA35=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||2|=INDEX($A$11:$A$14,MATCH(Z43,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA43=$D$17:$D$22)+(AA43=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||
|||||||||||||||||||||||||Node|ID|Strut
|||||||||||||||||||||||||3|=INDEX($A$11:$A$14,MATCH(Z51,$B$11:$B$14,0),1)|"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
|||||||||||||||||||||||||||"{=IFERROR(INDIRECT(""B""&SMALL(IF((AA51=$D$17:$D$22)+(AA51=$E$17:$E$22),ROW($D$17:$D$22),""""),ROW($D$17:$D$22)-16)),"""")}"
Source Link
Eric Marceau
Eric Marceau
  • 312
  • 3
  • 12

Creating an array with a reduced set of elements matching existence of criterion in multiple columns

I want to do this on a much larger problem, but chose the simplest example for a proof of concept.

At the bottom is a CSV of my file (with formulas).

I have data for points (vertices of tetrahedron): enter image description here

I have a set of derived data for struts (edges of tetrahedron): enter image description here

I am trying to generate "adaptively" and "dynamically" the array corresponding to Strut IDs for all struts that have a match under either of columns "S" or "E" (not a single column lookup) for label "D", and report the strut ID for the corresponding struts.

I would like the result to look like this:

enter image description here

I would also like to be able to set up similar lookup and tables for other nodes directly below that first result, without positional conflict. From what I've seen, pivot tables will not offer the means of a solution.

I saw a reference to this but there isn't enough information there for me to understand that approach, nor to apply it.

Can anyone offer a solution to my problem?

CSV (data)

||||||||||||||||||||||||
||||||Circumradius|1|||||||||||||||||
||||||Edge Length|=H2*4/SQRT(6)|||||||||||||||||
||||||Face Height|=H3*SIN(60*PI()/180)|||||||||||||||||
||||||Tet Height|=SQRT(6)*H3/3|||||||||||||||||
||||||Centroid|=H4/3|||||||||||||||||
||||||||||||||||||||||||
||||||||||||||||||||||||
||||||||||||||||||||||||
ID|Node|G_ID||||x|y|z||||||||||||||||
A|1|A||||=H4-H6|0|0||||||||||||||||
B|2|B||||=-H6|=H3/2|0||||||||||||||||
C|3|C||||=-H6|=-H3/2|0||||||||||||||||
D|4|D||||0|0|=H5||||||||||||||||
||||||||||||||||||||||||
ID|Strut|G_ID|S|E||Vx|Vy|Vz||Length||Sx|Sy|Sz||Ex|Ey|Ez||||||
AB|1|AB|A|B||=Q17-M17|=R17-N17|=S17-O17||=SQRT( (G17)^2 + (H17)^2 + (I17)^2 )||=VLOOKUP($D17,$A$11:$I$14,7,0)|=VLOOKUP($D17,$A$11:$I$14,8,0)|=VLOOKUP($D17,$A$11:$I$14,9,0)||=VLOOKUP($E17,$A$11:$I$14,7,0)|=VLOOKUP($E17,$A$11:$I$14,8,0)|=VLOOKUP($E17,$A$11:$I$14,9,0)||||||
BC|2|BC|B|C||=Q18-M18|=R18-N18|=S18-O18||=SQRT( (G18)^2 + (H18)^2 + (I18)^2 )||=VLOOKUP($D18,$A$11:$I$14,7,0)|=VLOOKUP($D18,$A$11:$I$14,8,0)|=VLOOKUP($D18,$A$11:$I$14,9,0)||=VLOOKUP($E18,$A$11:$I$14,7,0)|=VLOOKUP($E18,$A$11:$I$14,8,0)|=VLOOKUP($E18,$A$11:$I$14,9,0)||||||
CA|3|CA|C|A||=Q19-M19|=R19-N19|=S19-O19||=SQRT( (G19)^2 + (H19)^2 + (I19)^2 )||=VLOOKUP($D19,$A$11:$I$14,7,0)|=VLOOKUP($D19,$A$11:$I$14,8,0)|=VLOOKUP($D19,$A$11:$I$14,9,0)||=VLOOKUP($E19,$A$11:$I$14,7,0)|=VLOOKUP($E19,$A$11:$I$14,8,0)|=VLOOKUP($E19,$A$11:$I$14,9,0)||||||
DA|4|DA|D|A||=Q20-M20|=R20-N20|=S20-O20||=SQRT( (G20)^2 + (H20)^2 + (I20)^2 )||=VLOOKUP($D20,$A$11:$I$14,7,0)|=VLOOKUP($D20,$A$11:$I$14,8,0)|=VLOOKUP($D20,$A$11:$I$14,9,0)||=VLOOKUP($E20,$A$11:$I$14,7,0)|=VLOOKUP($E20,$A$11:$I$14,8,0)|=VLOOKUP($E20,$A$11:$I$14,9,0)||||||
DB|5|DB|D|B||=Q21-M21|=R21-N21|=S21-O21||=SQRT( (G21)^2 + (H21)^2 + (I21)^2 )||=VLOOKUP($D21,$A$11:$I$14,7,0)|=VLOOKUP($D21,$A$11:$I$14,8,0)|=VLOOKUP($D21,$A$11:$I$14,9,0)||=VLOOKUP($E21,$A$11:$I$14,7,0)|=VLOOKUP($E21,$A$11:$I$14,8,0)|=VLOOKUP($E21,$A$11:$I$14,9,0)||||||"=SMALL(IF(X25=B17:B22, ROW($D$17:$E$22)-2,""""))"
DC|6|DC|D|C||=Q22-M22|=R22-N22|=S22-O22||=SQRT( (G22)^2 + (H22)^2 + (I22)^2 )||=VLOOKUP($D22,$A$11:$I$14,7,0)|=VLOOKUP($D22,$A$11:$I$14,8,0)|=VLOOKUP($D22,$A$11:$I$14,9,0)||=VLOOKUP($E22,$A$11:$I$14,7,0)|=VLOOKUP($E22,$A$11:$I$14,8,0)|=VLOOKUP($E22,$A$11:$I$14,9,0)||||||
||||||||||||||||||||||||
||||||||||||||||||||||Node||Struts
||||||||||||||||||||||4|=INDEX(A11:A14,MATCH(W25,B11:B14,0),1)|4
||||||||||||||||||||||||5
||||||||||||||||||||||||6