Forum Discussion
Nested IF Statement? Expert needed to review
That will really help us to help you
Thanks
Wyn
Hi 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. There is a nesting issue in your formula and also unwanted "-"s. Atleast this formula doesn't give any syntax errors.
On Cell AZ174 -
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))
I got the same answer on cell AZ174 i.e. (211,935). It's not clear what is the expected value. It's also not clear where can we check your formula against different conditions