Worlds Longest Excel Formula!

This was a solution to a problem excel should have never been used for. I was told not to use our robust and expensive reporting tool because a manager wanted to “keep it in excel”. The spreadsheet is ridiculous and this is just one obscenity.

I would like to introduce you to what could be the longest excel formula in the world. It likes long walks on the beach, summing, counting, offsetting, matching, indirect functions, if statements, row functions and probably more if I reread it, which I will never do again. Do not get to close or it will consume you as it did me.

=SUMIFS(OFFSET(INDIRECT("H"& MATCH('Line
Targets'!B1,B8:B91,0)+ROW(B8),TRUE),IF(ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0)))<10,-ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0))),-10),0,IF(ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0)))<10,ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0))),10),1),OFFSET(INDIRECT("B"& MATCH('Line
Targets'!B1,B8:B91,0)+ROW(B8),TRUE),-IF(ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0)))<10,ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0))),10),0,IF(ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0)))<10,ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0))),10),1),"<="&'Line
Targets'!$B$1)/COUNTIFS(OFFSET(INDIRECT("B"& MATCH('Line
Targets'!B1,B8:B91,0)+ROW(B8),TRUE),-IF(ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0)))<10,ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0))),10),0,IF(ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0)))<10,ROW( INDIRECT("b"& MATCH('Line
Targets'!B1,B8:B91,0))),10),1),"<="&'Line Targets'!$B$1)

signed,
a broken man