Can you shrink this monster formula?

[ElRicky]

I believe myself to be an above average user. I come to this site to learn more and more but I don't seem to understand a lot of things about Excel.

I recently took over a few workbooks for budgeting and they seem to work well for small companies but not for the larger ones. For the larger ones, we resort to having about 10 to 15 workbooks open and even on i7, the computer will crash once in a while.

I need help reducing the following formulas and hopefully this will prevent the computer from crashing and allow our team to make changes without waiting a few minutes for workbooks to update.

1) =IFERROR(VALUE(INDIRECT("'"&'Consolidated Data'!$B$5&""&MID(CELL("filename"),FIND("]",CELL("filename"))+1,50)&"'!"&ADDRESS(ROW(L142),COLUMN(L142))))+VALUE(INDIRECT("'"&'Consolidated Data'!$B$6&""&MID(CELL("filename"),FIND("]",CELL("filename"))+1,50)&"'!"&ADDRESS(ROW(L142),COLUMN(L142))))+VALUE(INDIRECT("'"&'Consolidated Data'!$B$7&""&MID(CELL("filename"),FIND("]",CELL("filename"))+1,50)&"'!"&ADDRESS(ROW(L142),COLUMN(L142))))+VALUE(INDIRECT("'"&'Consolidated Data'!$B$8&""&MID(CELL("filename"),FIND("]",CELL("filename"))+1,50)&"'!"&ADDRESS(ROW(L142),COLUMN(L142))))+VALUE(INDIRECT("'"&'Consolidated Data'!$B$9&""&MID(CELL("filename"),FIND("]",CELL("filename"))+1,50)&"'!"&ADDRESS(ROW(L142),COLUMN(L142))))+VALUE(INDIRECT("'"&'Consolidated Data'!$B$10&""&MID(CELL("filename"),FIND("]",CELL("filename"))+1,50)&"'!"&ADDRESS(ROW(L142),COLUMN(L142)))),0)

2) =IF(H60=1,IF($L60>1456,IF($F60=1,IF($D60=1,+$M60*(1456-$K60-$J60)/12*(1+$T$6),$M60*(1456-$K60-$J60)/12),IF($G60=1,0,IF($F60<>1,0,IF($D60=1,+$M60*(1456-$K60-$J60)/12*(1+$T$6),$M60*(1456-$K60-$J60)/12)))),IF($F60=1,IF($D60=1,+$M60*($L60-$K60-$J60)/12*(1+$T$6),$M60*($L60-$K60-$J60)/12),IF($G60=1,0,IF($F60<>1,0,IF($D60=1,+$M60*($L60-$K60-$J60)/12*(1+$T$6),$M60*($L60-$K60-$J60)/12))))),IF($L60>2080,IF($F60=1,IF($D60=1,+$M60*(2080-$K60-$J60)/12*(1+$T$6),$M60*(2080-$K60-$J60)/12),IF($G60=1,0,IF($F60<>1,0,IF($D60=1,+$M60*(2080-$K60-$J60)/12*(1+$T$6),$M60*(2080-$K60-$J60)/12)))),IF($F60=1,IF($D60=1,+$M60*($L60-$K60-$J60)/12*(1+$T$6),$M60*($L60-$K60-$J60)/12),IF($G60=1,0,IF($F60<>1,0,IF($D60=1,+$M60*($L60-$K60-$J60)/12*(1+$T$6),$M60*($L60-$K60-$J60)/12))))))

Thank you in advance.

 

[Hui]

Can you

1. Describe what each formula does?
2. Post the 2 files

 

[Nebu]

Hi:

I addition to what Hui mentioned make use of helper columns to make the formulas more simple and easy to comprehend.

Thanks

 

[narunfca]

For 1st formula need example files.

Regarding 2nd Formula if $F60<>0 answer is 0 regardless the value of $G60. Check it.

Simplified formula if the above is correct.

=$M60*(MIN($L60,IF(H60=1,1456,2080))-$K60-$J60)/12*IF($D60=1,(1+$T$60),1)*(($F60=1)*1)

 

[ElRicky]

Thank you all. I will have to clear with my boss for the sample files but it should be alright. Thanks.

 

[ElRicky]

Spoke to the powers of be and unfortunately, I can not share the files. I will re-post another question or add to this one if I can share something else.

Thank you all for help though.

And thank you to narunfca for simplifying the formula.

 

[David Evans]

Spoke to the powers of be and unfortunately, I can not share the files. I will re-post another question or add to this one if I can share something else.

Thank you all for help though.

And thank you to narunfca for simplifying the formula.

Create Dummy file(s) with all the same features but nothing of any confidential nature - that should allay their fears that we're going to steal your corporate secrets ...

 

[bosco_yip]

Untested, try this in replace of the 1st formula :

=IFERROR(SUMPRODUCT(N(INDIRECT("'"&'Consolidated Data'!$B$5:$B$10&MID(CELL("filename"),FIND("]",CELL("filename"))+1,50)&"'!L142"))),0)

Regards
Bosco