Excel Level-Up: Day 6

Evaluating Formula

How do you understand a super long formula like this?

=IF(OR($R150="",W$148=""),"",IF(2016+$P$150-W$148>$P$159,"",MAX(IF(COUNT($S$148:W$148)=COUNT($S$148:$AB$148),OFFSET($X$99:$X$108,($P$159-COUNT($S$148:W$148)),0,1,1),IF((1-OFFSET($AF$149:$AF$158,($P$159-$P$150-COUNT($S$148:W$148)+1),0,1,1))=0,IF((OFFSET($X$99:$X$108,($P$159-$P$150-COUNT($S$148:W$148)+1),0,1,1))=0,0,IF($P$150>1,0,OFFSET($X$99:$X$108,($P$159-$P$150-COUNT($S$148:W$148)+1),0,1,1))),IF($R150-W$148=$P$159,IF(OFFSET($AF$149:$AF$158,($P$159-COUNT($S$148:W$148)),0,1,1)=1,0,(1-OFFSET($AF$149:$AF$158,0,0,1,1))/(1-OFFSET($AF$149:$AF$158,($P$159-COUNT($S$148:W$148)),0,1,1))*OFFSET($X$99:$X$108,($P$159-COUNT($S$148:W$148)),0,1,1)),IF((OFFSET($AF$149:$AF$158,($P$159-COUNT($S$148:W$148)),0,1,1))=1,0,(OFFSET($AF$149:$AF$158,($P$159-$P$150-COUNT($S$148:W$148)),0,1,1)-OFFSET($AF$149:$AF$158,($P$159-$P$150-COUNT($S$148:W$148)+1),0,1,1))/(1-OFFSET($AF$149:$AF$158,($P$159-COUNT($S$148:W$148)),0,1,1))*OFFSET($X$99:$X$108,($P$159-COUNT($S$148:W$148)),0,1,1))))),0)))

Luckily, Excel understands your difficulty and so has built-in an awesome tool called “Evaluate Formula”.

Go to “Formula” tab in the ribbon, “Evaluate Formula” is in the Formula Auditing section.

This function helps you breaking down the long formula into smaller digestible bits. Each time, you click the “Evaluate” button, it will solve the next small part of the formula.

No freak out anymore when you see complicated formula in the future.


Go back to previous awesome trick: Excel Level-Up: Day 5
Proceed to next awesome trick: Excel Level-Up: Day 7