Shortening an ultra long formula

beefjerky8805

New Member
Joined
Jun 30, 2019
Messages
4
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,"")
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,624
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

New Member
Joined
Jun 30, 2019
Messages
4
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:

1.png


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

2.png


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

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
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
Code:
IF(OR(T68>0,U68>0,V68>0,W68>0,X68>0),S68
With
Code:
IF(SUM(T68:X68)>0,S68
Or if you have negative numbers, then
Code:
IF(MAX(T68:X68)>0,S68
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,624

ADVERTISEMENT

Re: Help with shortening an ultra long formula

If I understand your requirements correctly, try this:

BCDEFGHSTUVWX
35Ext. Header 2x'sfl1fl2fl3fl3fl5
362 x 8 x 8' SYP#2300210
372 x 12 x 12' SYP#2025000
382 x 12 x 10' SYP#204000
39
40
41
66ext hdr calc2fl1fl2fl3fl4fl5
672 x 8 x 16' SYP
682 x 8 x 14' SYP
692 x 8 x 12' SYP
702 x 8 x 10' SYP
712 x 8 x 8' SYP#2321
722 x 10 x 16' SYP
732 x 10 x 14' SYP
742 x 10 x 12' SYP
752 x 10 x 10' SYP
762 x 10 x 8' SYP
772 x 12 x 16' SYP
782 x 12 x 14' SYP
792 x 12 x 12' SYP#225
802 x 12 x 10' SYP#24
812 x 12 x 8' SYP

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C36=IF($B36="","",INDEX(T$67:T$81,MATCH($B36,$S$67:$S$81,0)))

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B36{=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))),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>




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.
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,624
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

New Member
Joined
Jun 30, 2019
Messages
4

ADVERTISEMENT

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

New Member
Joined
Jun 30, 2019
Messages
4
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
Code:
IF(OR(T68>0,U68>0,V68>0,W68>0,X68>0),S68
With
Code:
IF(SUM(T68:X68)>0,S68
Or if you have negative numbers, then
Code:
IF(MAX(T68:X68)>0,S68

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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,737
Messages
5,597,826
Members
414,180
Latest member
Sir Khaya

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
Top