Forum Discussion
Nested IF Statement? Expert needed to review
Hi, Can anyone see what I have done wrong?
=IF(SUM(-$AL$174:AY174)+SUM(-AY59:AY61)+SUM(-AZ59:$CE$61))<($C$173*$D$173),SUM(AY59:$AY$61),IF(($C$173*$D$173)-SUM(-$AL$174:AY174)-SUM(-BA59:$CE$61))>0,(SUM($C$173*$D$173)-SUM($AL$174:AY174)-SUM(BA59:$CE$61)),0))
4 Replies
- Can you attach a file with the formula in context, then explain what you are trying to do, what you expect the answer to be and what's going wrong.
That will really help us to help you
Thanks
WynHi Wyn
In Cell AZ174 of the attached, I am trying to set up a way to ensure an expense claim on a finance facility does not exceed the limit of the facility given previous drawdowns and future costs to complete. The formula I posted if effectively trying to put into practice the following statement:
If (Drawdowns to date + Expenses this month+ Future Costs to Complete) < Facility Limit, Expenses this month, IF( ((Facility Limit)-(Drawdowns to Date)-(Future Costs to Complete)) > 0, ((Facility Limit)-(Drawdowns to Date)-(Future Costs to Complete)) ,0))
Outcomes expected:
If the claim this month does not result in the facility cap being breached then the result is the full claim
If the claim this month does mean the cap is breached then the claim is capped to the extent the facility becomes fully drawn (so part but not the whole claim is the result)
Alternatively if the facility has reached its cap before this months claim then the claim this month would be zero.
Your help is greatly appreciated
- So, do you want to replace the current reference to AZ132 with this full formula?
I'd strongly advise against this type of long nested formula
Much better to break down the separate elements into several individual rows of calculations. Much easier to build, spot issues, debug, and for the next user to understand it.
