+ Reply to Thread
Results 1 to 3 of 3

Explain the calculations please...

  1. #1
    Registered User
    Join Date
    04-27-2012
    Location
    Melbourne, Oz
    MS-Off Ver
    Excel 2007
    Posts
    80

    Explain the calculations please...

    I inherited this spread sheet. Could someone explain to me what is happening by way of the calculations in column L and column Q? No Clue at all.

    Thank you

    Raghu PrabhuIncrement.zip

  2. #2
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    Re: Explain the calculations please...

    Column L
    =IF(K3<>"N/A",IF(I3 = "CANCELLED", G3 +Q3, IF(OR(P3+1-G3>(I3+1-H3)+Q3,P3=""),G3+Q3+S3,"N/A")),"N/A")

    If K3 (the New Incr) is not N/A then

    If I3 (LWOP CEASE) is CANCELLED then use the Start Incr Date in G3 plus the number of days in Q (which in this case is 17-Jan-04 plus 366 days)
    Else

    If P3 (PTLWOP) plus 1 day minus G3 (Start Incr Date) is greater than I3 (LWOP CEASE) plus 1 day minus H3 (LWOP COMMENCE date) plus the number of days in Q (which in this case is plus 366 days)
    ......OR if P3 (DISC/PROM DATE) is blank
    then
    G3 (Start Incr Date) plus the number of days in Q (which in this case is plus 366 days) + plus the number of days in S (which in this case is Zero days)

    Else N/A

    The last NA simply means that the first line “If K3 (the New Incr) is not N/A” was in fact NA so don’t do anything of the calculations in the middle.



    Column Q
    =DATE(IF(D3="FLYING",YEAR(G3)+2,YEAR(G3)+1),MONTH(G3),DAY(G3))-G3

    If D3 (the Rank) = FLYING then
    add two years to the date in G3 (Start Incr Date)
    If NOT FLYING add one year to the date in G3 (Start Incr Date)

    Then subtract G3 (Start Incr Date) from this date.

    Basically it’s counting the number of days in the next two years for Flying Rank or the next one year for all other Ranks, including the extra day for leap year instead of just assuming all years are 365 days.

  3. #3
    Registered User
    Join Date
    04-27-2012
    Location
    Melbourne, Oz
    MS-Off Ver
    Excel 2007
    Posts
    80

    Re: Explain the calculations please...

    Thanks Carsto. I am now able to get on with my work.

    Cheers
    Raghu Prabhu

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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