+ Reply to Thread
Results 1 to 3 of 3

Help Understanding Nested IF Statements in Attached

  1. #1
    Registered User
    Join Date
    08-01-2016
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    1

    Help Understanding Nested IF Statements in Attached

    Hello,

    I need help understanding the nested IF Statement below in the attached XLS documents. Can anyone advise?

    =IF($E3=0,0,IF(IF(((Z$2-$A3)>=365),IF((((Z$2-$A3)-365)<=(Z$2-Y$2)),((140/12)*(((Z$2-$A3)-365)/(Z$2-Y$2))),IF(((Z$2-$A3)>=1096),((140/12)*((1096-(Y$2-$A3))/((Z$2-Y$2)+1))),(140/12))),IF((Y$2<$A3),IF(($A3<=Z$2),($E3-280),0),0))<0,0,IF(((Z$2-$A3)>=365),IF((((Z$2-$A3)-365)<=(Z$2-Y$2)),((140/12)*(((Z$2-$A3)-365)/(Z$2-Y$2))),IF(((Z$2-$A3)>=1096),((140/12)*((1096-(Y$2-$A3))/((Z$2-Y$2)+1))),(140/12))),IF((Y$2<$A3),IF(($A3<=Z$2),($E3-280),0),0))*$B3))
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-30-2015
    Location
    Netherlands
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    145

    Re: Help Understanding Nested IF Statements in Attached

    Hi,

    The nested IFS are to determine the correct aging and place a revenue amount deducted with 260,- per item in the correct month; next part of the formula will take the "amount of revenue check" and amortize it over a period.

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Help Understanding Nested IF Statements in Attached

    If the formula is not performing as expected then an explanation of what you need it to do would make it easier for us to help you with it.

    That formula is badly written with a lot of unnecessary repeated calculations. While not impossible, trying to follow it accurately is no easy task.

    Also, the formula as based on 30 day months, which is why there is a discrepancy between the figures in columns D and F.

    edit:-

    More accurate version, if that is what you need.

    Enter the formula into Y3 then copy right and down to fill the table.

    =CHOOSE(MATCH(COLUMNS($Y$2:Y$2)-MATCH(EOMONTH($A3,0),$Y$2:$BP$2),{-1000,0,1,12,13,36,37}),0,($E3-280)*$B3,0,(1-(DAY($A3)/DAY(Y$2)))*(280/24)*$B3,(280/24)*$B3,(DAY($A3)/DAY(Y$2))*(280/24)*$B3,0)

    I had extended the dates in the top row while testing the formula, the range shown in red above should be adjusted to cover all of the dates in the top row of your actual data.
    Last edited by jason.b75; 08-04-2016 at 09:06 AM.

+ 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. Date/Time with True and False statements attached.
    By Claybri in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-17-2016, 11:49 AM
  2. [SOLVED] Need Help with Nested IF, OR, AND Statement > EXAMPLE Attached
    By mikederaco in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2015, 01:29 PM
  3. Replies: 5
    Last Post: 05-16-2014, 03:45 PM
  4. New to array formulas. Not understanding nested sum if
    By stevekbluesnap in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2014, 05:50 PM
  5. Multiple nested IF statements and AND statements
    By TonyGetz in forum Excel General
    Replies: 2
    Last Post: 12-14-2010, 03:07 AM
  6. Replies: 12
    Last Post: 05-15-2009, 08:38 AM
  7. Understanding if and countif statements
    By Tweetie in forum Excel General
    Replies: 2
    Last Post: 03-24-2009, 04:36 PM

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