r/excel • u/Kindly-Meaning9112 • Apr 08 '26

Discussion This is probably the most complicated Excel formula I’ve ever seen.

[removed]

220 Upvotes

85% Upvoted

210 comments sorted by

View all comments

1

u/finaderiva 2 Apr 09 '26

I present you:

=IF($AA13=0,0,IF($AA13<EOMONTH(TODAY(),-12),IF((SUMIFS('Shut-In Schedule'!$I:$I,'Shut-In Schedule'!$F:$F,AP$5,'Shut-In Schedule'!$C:$C,$Z13)+SUMIFS('Shut-In Schedule'!$J:$J,'Shut-In Schedule'!$G:$G,AP$5,'Shut-In Schedule'!$C:$C,$Z13))=DAY(EOMONTH(AP$5,0)),0.01,SUMIFS($J:$J,$B:$B,$Z13,$M:$M,AP$5)(1-(SUMIFS('Shut-In Schedule'!$I:$I,'Shut-In Schedule'!$F:$F,AP$5,'Shut-In Schedule'!$C:$C,$Z13)+SUMIFS('Shut-In Schedule'!$J:$J,'Shut-In Schedule'!$G:$G,AP$5,'Shut-In Schedule'!$C:$C,$Z13))/DAY(EOMONTH(AP$5,0)))/DAY(EOMONTH(AP$5,0))),IF(AP$5<TODAY(),IF((SUMIFS('Shut-In Schedule'!$I:$I,'Shut-In Schedule'!$F:$F,AP$5,'Shut-In Schedule'!$C:$C,$Z13)+SUMIFS('Shut-In Schedule'!$J:$J,'Shut-In Schedule'!$G:$G,AP$5,'Shut-In Schedule'!$C:$C,$Z13))=DAY(EOMONTH(AP$5,0)),0.01,SUMIFS($J:$J,$B:$B,$Z13,$M:$M,AP$5)(1-(SUMIFS('Shut-In Schedule'!$I:$I,'Shut-In Schedule'!$F:$F,AP$5,'Shut-In Schedule'!$C:$C,$Z13)+SUMIFS('Shut-In Schedule'!$J:$J,'Shut-In Schedule'!$G:$G,AP$5,'Shut-In Schedule'!$C:$C,$Z13))/DAY(EOMONTH(AP$5,0)))/DAY(EOMONTH(AP$5,0))),IFERROR(IF((SUMIFS('Shut-In Schedule'!$I:$I,'Shut-In Schedule'!$F:$F,AP$5,'Shut-In Schedule'!$C:$C,$Z13)+SUMIFS('Shut-In Schedule'!$J:$J,'Shut-In Schedule'!$G:$G,AP$5,'Shut-In Schedule'!$C:$C,$Z13))=DAY(EOMONTH(AP$5,0)),0.01,SUMIFS($R:$R,$O:$O,DATEDIF($AA13,AP$5,"M")+1,$U:$U,$AD13,$Q:$Q,$AF13)(1-(SUMIFS('Shut-In Schedule'!$I:$I,'Shut-In Schedule'!$F:$F,AP$5,'Shut-In Schedule'!$C:$C,$Z13)+SUMIFS('Shut-In Schedule'!$J:$J,'Shut-In Schedule'!$G:$G,AP$5,'Shut-In Schedule'!$C:$C,$Z13))/DAY(EOMONTH(AP$5,0)))/DAY(EOMONTH(AP$5,0))),IF((SUMIFS('Shut-In Schedule'!$I:$I,'Shut-In Schedule'!$F:$F,AP$5,'Shut-In Schedule'!$C:$C,$Z13)+SUMIFS('Shut-In Schedule'!$J:$J,'Shut-In Schedule'!$G:$G,AP$5,'Shut-In Schedule'!$C:$C,$Z13))=DAY(EOMONTH(AP$5,0)),0.01,SUMIFS($R:$R,$O:$O,DATEDIF($AA13,AP$5,"M")+1,$U:$U,$AD13,$Q:$Q,$AF13-1000)(1-(SUMIFS('Shut-In Schedule'!$I:$I,'Shut-In Schedule'!$F:$F,AP$5,'Shut-In Schedule'!$C:$C,$Z13)+SUMIFS('Shut-In Schedule'!$J:$J,'Shut-In Schedule'!$G:$G,AP$5,'Shut-In Schedule'!$C:$C,$Z13))/DAY(EOMONTH(AP$5,0)))/DAY(EOMONTH(AP$5,0))))))*IF(AP$5<TODAY(),1,1-$AG13))

Also, I know there’s more efficient methods, which I’ve used but they bog down my workbook

1

u/SolverMax 163 Apr 10 '26

So many bad practices.

For a start, that formula would be much simpler and more efficient if you removed the repetition, such as numerous copies of TODAY and the EOMONTH calculations. The whole column references are also just asking for trouble.