Shortening an ultra long formula

[beefjerky8805]

Hi! I have a formula that I use to determine the text of a cell based on the text of the previous cell, and also the values of a table of numbers. It works, but I am wondering if there is a more efficient way of doing this, because I need to make one that does the same thing, but has to evaluate many many more cells. Thanks!

=SWITCH(B36,S67,IF(OR(T68>0,U68>0,V68>0,W68>0,X68>0),S68,IF(OR(T69>0,U69>0,V69>0,W69>0,X69>0),S69,IF(OR(T70>0,U70>0,V70>0,W70>0,X70>0),S70,IF(OR(T71>0,U71>0,V71>0,W71>0,X71>0),S71,IF(OR(T72>0,U72>0,V72>0,W72>0,X72>0),S72,IF(OR(T73>0,U73>0,V73>0,W73>0,X73>0),S73,IF(OR(T74>0,U74>0,V74>0,W74>0,X74>0),S74,IF(OR(T75>0,U75>0,V75>0,W75>0,X75>0),S75,IF(OR(T76>0,U76>0,V76>0,W76>0,X76>0),S76,IF(OR(T77>0,U77>0,V77>0,W77>0,X77>0),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81)))))))))))))),S68,IF(OR(T69>0,U69>0,V69>0,W69>0,X69>0),S69,IF(OR(T70>0,U70>0,V70>0,W70>0,X70>0),S70,IF(OR(T71>0,U71>0,V71>0,W71>0,X71>0),S71,IF(OR(T72>0,U72>0,V72>0,W72>0,X72>0),S72,IF(OR(T73>0,U73>0,V73>0,W73>0,X73>0),S73,IF(OR(T74>0,U74>0,V74>0,W74>0,X74>0),S74,IF(OR(T75>0,U75>0,V75>0,W75>0,X75>0),S75,IF(OR(T76>0,U76>0,V76>0,W76>0,X76>0),S76,IF(OR(T77>0,U77>0,V77>0,W77>0,X77>0),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81))))))))))))),S69,IF(OR(T70>0,U70>0,V70>0,W70>0,X70>0),S70,IF(OR(T71>0,U71>0,V71>0,W71>0,X71>0),S71,IF(OR(T72>0,U72>0,V72>0,W72>0,X72>0),S72,IF(OR(T73>0,U73>0,V73>0,W73>0,X73>0),S73,IF(OR(T74>0,U74>0,V74>0,W74>0,X74>0),S74,IF(OR(T75>0,U75>0,V75>0,W75>0,X75>0),S75,IF(OR(T76>0,U76>0,V76>0,W76>0,X76>0),S76,IF(OR(T77>0,U77>0,V77>0,W77>0,X77>0),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81)))))))))))),S70,IF(OR(T71>0,U71>0,V71>0,W71>0,X71>0),S71,IF(OR(T72>0,U72>0,V72>0,W72>0,X72>0),S72,IF(OR(T73>0,U73>0,V73>0,W73>0,X73>0),S73,IF(OR(T74>0,U74>0,V74>0,W74>0,X74>0),S74,IF(OR(T75>0,U75>0,V75>0,W75>0,X75>0),S75,IF(OR(T76>0,U76>0,V76>0,W76>0,X76>0),S76,IF(OR(T77>0,U77>0,V77>0,W77>0,X77>0),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81))))))))))),S71,IF(OR(T72>0,U72>0,V72>0,W72>0,X72>0),S72,IF(OR(T73>0,U73>0,V73>0,W73>0,X73>0),S73,IF(OR(T74>0,U74>0,V74>0,W74>0,X74>0),S74,IF(OR(T75>0,U75>0,V75>0,W75>0,X75>0),S75,IF(OR(T76>0,U76>0,V76>0,W76>0,X76>0),S76,IF(OR(T77>0,U77>0,V77>0,W77>0,X77>0),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81)))))))))),S72,IF(OR(T73>0,U73>0,V73>0,W73>0,X73>0),S73,IF(OR(T74>0,U74>0,V74>0,W74>0,X74>0),S74,IF(OR(T75>0,U75>0,V75>0,W75>0,X75>0),S75,IF(OR(T76>0,U76>0,V76>0,W76>0,X76>0),S76,IF(OR(T77>0,U77>0,V77>0,W77>0,X77>0),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81))))))))),S73,IF(OR(T74>0,U74>0,V74>0,W74>0,X74>0),S74,IF(OR(T75>0,U75>0,V75>0,W75>0,X75>0),S75,IF(OR(T76>0,U76>0,V76>0,W76>0,X76>0),S76,IF(OR(T77>0,U77>0,V77>0,W77>0,X77>0),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81)))))))),S74,IF(OR(T75>0,U75>0,V75>0,W75>0,X75>0),S75,IF(OR(T76>0,U76>0,V76>0,W76>0,X76>0),S76,IF(OR(T77>0,U77>0,V77>0,W77>0,X77>0),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81))))))),S75,IF(OR(T76>0,U76>0,V76>0,W76>0,X76>0),S76,IF(OR(T77>0,U77>0,V77>0,W77>0,X77>0),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81)))))),S76,IF(OR(T77>0,U77>0,V77>0,W77>0,X77>0),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81))))),S77,IF(OR(T78>0,U78>0,V78>0,W78>0,X78>0),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81)))),S78,IF(OR(T79>0,U79>0,V79>0,W79>0,X79>0),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81))),S79,IF(OR(T80>0,U80>0,V80>0,W80>0,X80>0),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81)),S80,IF(OR(T81>0,U81>0,V81>0,W81>0,X81>0),S81),S81,"")

 

[Eric W]

Re: Help with shortening an ultra long formula

Welcome to the forum.

I can almost guarantee that there's a better way to write that formula. But rather than expecting us to decipher the formula, could you show us a sample of your sheet, including the tables you're referencing, and explain how to get your desired results?

 

[beefjerky8805]

Re: Help with shortening an ultra long formula

Welcome to the forum.

I can almost guarantee that there's a better way to write that formula. But rather than expecting us to decipher the formula, could you show us a sample of your sheet, including the tables you're referencing, and explain how to get your desired results?

Sure. Here is the table the formula is in:

The formula is from B37. Here is the table that B37 is pulling from:

The object is to consolidate board sizes that have quantities greater than zero. B36 is a much shorter formula, because it simply needs to display the first row that has a quantity, but B37-B50 each have to find the next row with a quantity. The formula for B37-B50 is identical except for the first cell reference in the switch statement. For example, the formula for B45 begins like this: =SWITCH(B44,S67,IF(OR(T68>0,U68>0,V68>0,W68>0,X68>0),S68,IF(OR(T69>0,U69>0,V69>. The formula could actually shrink the farther down the list I go; for example, B45 could start like this: =SWITCH(B44,S74,IF(OR(T75>0,U75>0,V75>0,W75>0,X75>0),S75,IF(OR(T76>0,U76>0,V76>.
Thank you for the speedy reply!

 

[JackDanIce]

Re: Help with shortening an ultra long formula

It's too long to decipher, but a quick spot suggests some reduction may be achieved replacing

IF(OR(T68>0,U68>0,V68>0,W68>0,X68>0),S68

With

IF(SUM(T68:X68)>0,S68

Or if you have negative numbers, then

IF(MAX(T68:X68)>0,S68

 

[Eric W]

Re: Help with shortening an ultra long formula

If I understand your requirements correctly, try this:

	B	C	D	E	F	G	H	S	T	U	V	W	X
Ext. Header 2x's	fl1	fl2	fl3	fl3	fl5
2 x 8 x 8' SYP#2
2 x 12 x 12' SYP#2
2 x 12 x 10' SYP#2
ext hdr calc2	fl1	fl2	fl3	fl4	fl5
2 x 8 x 16' SYP
2 x 8 x 14' SYP
2 x 8 x 12' SYP
2 x 8 x 10' SYP
2 x 8 x 8' SYP#2
2 x 10 x 16' SYP
2 x 10 x 14' SYP
2 x 10 x 12' SYP
2 x 10 x 10' SYP
2 x 10 x 8' SYP
2 x 12 x 16' SYP
2 x 12 x 14' SYP
2 x 12 x 12' SYP#2
2 x 12 x 10' SYP#2
2 x 12 x 8' SYP

<tbody>
[TD="align: center"]35[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]67[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]68[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]69[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]71[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"][/TD]

[TD="align: center"]72[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]73[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]74[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]76[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]79[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]81[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C36[/TH]
[TD="align: left"]=IF($B36="","",INDEX(T$67:T$81,MATCH($B36,$S$67:$S$81,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B36[/TH]
[TD="align: left"]{=IFERROR(INDEX($S:$S,SMALL(IF(MMULT((--($T$67:$X$81>0)),{1;1;1;1;1}),ROW($T$67:$T$81)),ROWS($B$36:$B36))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




This is my first stab at it, I have another idea that might work too. Put the array formula in C36, confirm with Control+Shift+Enter, then drag down the column as far as needed. Then put in the C36 formula (just Enter), and drag down and to the right as needed. This shows zeros instead of spaces, but we can adjust that later if this works for you.

In any event, this is far shorter than the original version! Let us know how it works.

 

[Eric W]

Re: Help with shortening an ultra long formula

This version of the C36 formula will not show 0 values. Enter it as before.

=IFERROR(1/(1/INDEX(T$67:T$81,MATCH($B36,$S$67:$S$81,0))),"")

 

[beefjerky8805]

Re: Help with shortening an ultra long formula

If I understand your requirements correctly, try this:

Thank you, thank you!! This helps so much. Works perfectly. What a better way to do it! Your revised method also works great!

 

[beefjerky8805]

Re: Help with shortening an ultra long formula

It's too long to decipher, but a quick spot suggests some reduction may be achieved replacing

IF(OR(T68>0,U68>0,V68>0,W68>0,X68>0),S68

With

IF(SUM(T68:X68)>0,S68

Or if you have negative numbers, then

IF(MAX(T68:X68)>0,S68

Ah, I did not think of that. That is much more efficient. Thank you!

 

[Eric W]

Re: Help with shortening an ultra long formula

Glad we could help!

 

[JackDanIce]

Re: Help with shortening an ultra long formula

I think @Eric W is far too humble, he did all the work, kudos should be all to him, but thank you for the "we" could help!

My suggestion didn't offer an actual solution, just a way to reduce logic evaluations, but if you learnt something from it, cool