How to Re-Write a Very Long Formula

[CurtisD]

Hello,

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

=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!

 

[offthelip]

what about trying a formual like this:

=iferror(MATCH($J156,AC156:AT156,0),MATCH($J156,AC156:AT156,1)+1)

 

[steve the fish]

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))

 

[Peter_SSs]

=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.

 

[CurtisD]

Hi Peter,
Pardon my typo. The formula is in $L156.
Thanks, Curtis

 

[CurtisD]

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

 

[CurtisD]

Hi Steve,

I'm confused about this part of your formula:

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

I haven't seen INDEX used in that way. Does the first parameter

--($J156<=AC156:AT156)

return a single number, or an array? Any explanation would be helpful.

Thanks!

 

[Peter_SSs]

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?

 

[CurtisD]

Hi Peter,
Yes, you are right. I'm sorry for the trouble. I made a mistake when I was testing.
Thanks!

 

[Peter_SSs]

Hi Peter,
Yes, you are right. I'm sorry for the trouble. I made a mistake when I was testing.
Thanks!

Cheers. Glad you got it sorted.