1. ## 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. ## 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. ## Re: Explain the calculations please...

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

Cheers
Raghu Prabhu

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

#### 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