+ Reply to Thread
Results 1 to 4 of 4

Simplify multiple IF and AND functions

  1. #1
    Registered User
    Join Date
    08-11-2014
    Location
    USA
    MS-Off Ver
    2013 Pro
    Posts
    26

    Question Simplify multiple IF and AND functions

    I have a Capital Investment Distribution table that I have been working on and the logic conditions that I have to apply seem to be getting excessive. I am wondering if there is possibly a simpler method that is escaping me.

    D3:M3 represents Capital available for Distribution at the end of each fiscal year.

    D4:M4 represents the minimum required Distribution Benchmark C2 plus any carryover from the prior year (negative value in ROW 6) if the amount available wasn't sufficient:

    =IF($C$2-D$6<$C$2,$C$2,$C$2-D$6)...

    (ignore D4 because there are no additional conditions for that cell)

    D5:M5 represents the actual Distribution amount paid provided that there is enough Capital available from ROW 3 and if there isn't then the amount is paid to the extent of Capital available not to exceed the amount in ROW 4:

    =IF(D$3<0,0,IF(D$3<=D$4,D$3,IF(D$4>$C$2,D$4,$C$2)))

    ROW 4 and ROW 5 aren't that bad and I can live with them but it's ROW 6 that is brutal.

    D6:M6 represents the remainder of the Distribution paid if the Capital available is sufficient to cover the amount from ROW 4, if Capital available is insufficient it results in a negative balance that is added back to ROW 4 +1COLUMN:

    =IF(AND(D$3<=0,C$6<0),D$5-D$4,IF(AND(D$3<=0,C$6>=0),D$5-D$4,IF(AND(C$6<0,D$4-D$5>0),D$5-D$4,IF(AND(C$6<0,D$4-D$5=0),D$3-D$5,D$3-D$5))))

    You can adjust the numbers (positive, negative, Zero) in ROW 3 to see how the conditions flow.

    I hope I have explained this sufficiently and it is fairly self-explanatory in the attached worksheet. Let me know if you need further explanation.

    example2.xlsx

    Thanks

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Simplify multiple IF and AND functions

    Hi,

    You can simplify your last formula as following:

    =IF(OR(AND(D$3<=0,C$6<0),AND(D$3<=0,C$6>=0),AND(C$6<0,D$4-D$5>0)),D$5-D$4,IF(AND(C$6<0,D$4-D$5=0),D$3-D$5,D$3-D$5))

  3. #3
    Registered User
    Join Date
    08-11-2014
    Location
    USA
    MS-Off Ver
    2013 Pro
    Posts
    26

    Re: Simplify multiple IF and AND functions

    I will give it a whirl, thanks for the help.

  4. #4
    Registered User
    Join Date
    08-11-2014
    Location
    USA
    MS-Off Ver
    2013 Pro
    Posts
    26

    Re: Simplify multiple IF and AND functions

    If anyone is interested I was able to make it even simpler, although it may be unique to my situation here it is:

    =IF(OR(D$3<=0,AND(C$6<0,D$4-D$5>0)),D$5-D$4,D$3-D$5)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Simplify Multiple Transactions
    By gtudor in forum Excel General
    Replies: 8
    Last Post: 03-11-2014, 02:01 PM
  2. [SOLVED] please help, problems with how to simplify the IF functions
    By herukuncahyono in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-12-2013, 11:09 PM
  3. Replies: 0
    Last Post: 05-14-2012, 09:32 AM
  4. Simplify dozens of IF functions in macro
    By Mladjone in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2011, 07:23 PM
  5. How to simplify my multiple checkboxes command
    By Malou in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-11-2009, 06:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1