How to Re-Write a Very Long Formula

CurtisD

New Member
Joined
Oct 4, 2019
Messages
15
Hello,

I have a spreadsheet with tens of thousands of formulas like the following. I've split it over multiple lines
for readability.


Code:
=IFERROR(IF(K156=0,0,
   IF($J156<=AC156,AC$2,
   IF($J156<=AD156,AD$2,
   IF($J156<=AE156,AE$2,
   IF($J156<=AF156,AF$2,
   IF($J156<=AG156,AG$2,
   IF($J156<=AH156,AH$2,
   IF($J156<=AI156,AI$2,
   IF($J156<=AJ156,AJ$2,
   IF($J156<=AK156,AK$2,
   IF($J156<=AL156,AL$2,
   IF($J156<=AM156,AM$2,
   IF($J156<=AN156,AN$2,
   IF($J156<=AO156,AO$2,
   IF($J156<=AP156,AP$2,
   IF($J156<=AQ156,AQ$2,
   IF($J156<=AR156,AR$2,
   IF($J156<=AS156,AS$2,
   IF($J156<=AT156,AT$2,1000)
   )))))))))))))))))),1000)


This formula is in cell $K156. The formula in cell $K157 is the same except the row number is 157.


The spreadsheet takes 10 minutes to load, and when a number in one of the cells referenced
in the formula changes there is a delay of several minutes while the sheet is re-calculated.


I am hoping to speed the loading time of the spreadsheet and the re-calculation time.


The basic idea is to find the first column (from AC to AT) with a number that $J156 is less than or equal to.
Then return the value in row 2 of that column. The numbers in columns AC to AT are in ascending order.


I tried using MATCH to find the column, the idea being I could use that offset to find the correct return value in
the range $AC2 to $AT2. I tried:


=MATCH($J156,AC156:AT156,1)


but that didn't work. When $J156 is not exactly equal to a number in the range the column returned
is one less than the correct value.


I imagine it doesn't take Excel very long to execute the long formula above. But perhaps the text length of
tens of thousands of formulas (and the file size) slows things down, and a more efficient formula might help.


I would be grateful for any thoughts on whether this formula could be improved or shortened, or if it wouldn't
make much difference.


Thanks!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
what about trying a formual like this:
Code:
=iferror(MATCH($J156,AC156:AT156,0),MATCH($J156,AC156:AT156,1)+1)
 
Upvote 0
See if this always produces the same results:

=IF(K156=0,0,IFERROR(INDEX(AC$2:AT$2,MATCH(1,INDEX(--($J156<=AC156:AT156),0),0)),1000))
 
Upvote 0
Code:
=IFERROR(IF([COLOR="#FF0000"][B][SIZE=4]K156[/SIZE][/B][/COLOR]=0,0,
   IF($J156<=AC156,AC$2,
   IF($J156<=AD156,AD$2,
   IF($J156<=AE156,AE$2,
   IF($J156<=AF156,AF$2,
   IF($J156<=AG156,AG$2,
   IF($J156<=AH156,AH$2,
   IF($J156<=AI156,AI$2,
   IF($J156<=AJ156,AJ$2,
   IF($J156<=AK156,AK$2,
   IF($J156<=AL156,AL$2,
   IF($J156<=AM156,AM$2,
   IF($J156<=AN156,AN$2,
   IF($J156<=AO156,AO$2,
   IF($J156<=AP156,AP$2,
   IF($J156<=AQ156,AQ$2,
   IF($J156<=AR156,AR$2,
   IF($J156<=AS156,AS$2,
   IF($J156<=AT156,AT$2,1000)
   )))))))))))))))))),1000)


This formula is in cell $K156.
Are you sure? That would produce a circular reference with the formula referring to itself.
 
Upvote 0
Hi Steve,

Thank you! Your formula works perfectly. But there's a problem if $J156 is blank. The original formula returns 1000, which is what I want.
Your formula returns the value in AC$2.

I would be grateful if you could suggest a correction that would return 1000 when $J156 is blank.

Thank you!

Sincerely, Curtis
 
Upvote 0
Hi Steve,

I'm confused about this part of your formula:

Code:
INDEX(--($J156<=AC156:AT156),0)

I haven't seen INDEX used in that way. Does the first parameter
Code:
--($J156<=AC156:AT156)
return a single number, or an array? Any explanation would be helpful.

Thanks!
 
Upvote 0
But there's a problem if $J156 is blank. The original formula returns 1000, which is what I want.
Your formula returns the value in AC$2.
I'm confused about that. For me,
- if J156 is completely empty & K156 not 0 then both Steve's formula and your original formula return the value in AC2.
- if J156 contains a formula that returns "" (so the cell appears blank) & K156 not 0 then both Steve's formula and your original formula return 1000

Can you clarify the J156 situation?
 
Last edited:
Upvote 0
Hi Peter,
Yes, you are right. I'm sorry for the trouble. I made a mistake when I was testing.
Thanks!
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

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