understanding this excel formula-CodePudding

=IF([@[CIRCUIT_ID]]="BTWD206969","",IF(OR([@[SUSPEND_DATE]]="CLOS",[@[SUSPEND_DATE]]="ENGC"),IF([@SCD]>$BG$1,IF([@[ORDER_TYPE]]="QCS924",VLOOKUP([@[ORDER_VALIDATED_DATE_TIME2]],'10G Apollo LD'!B:C,2,0),""),""),""))

Below I have broken down the formula, to try and understand it better:

=IF([@[CIRCUIT_ID]]="BTWD206969","" - My understanding is if 'circuit_ID column contains value BTWD206969 then return nothing,

IF(OR([@[SUSPEND_DATE]]="CLOS",[@[SUSPEND_DATE]]="ENGC"),IF([@SCD]>$BG$1 - If column suspend date = close or engc, or if any value in column SCD if bigger than the value in B1

IF([@[ORDER_TYPE]]="QCS924",VLOOKUP([@[ORDER_VALIDATED_DATE_TIME2]],'10G Apollo LD'!B:C,2,0),""),""),"")) - IF order type column is equal to QCS924 then VLOOKUP ORDER_VALIDATED_DATE_TIME2 column in tab 10G Apollo LD in column B:C, returning matching value in list C.

Can you give me your understanding of the above formula?

CodePudding user response：

The Excel IF statement, IF( condition, result if true, result if false) translates to a typical procedural language IF statement as: IF( condition, THEN, ELSE ). I end up with:

IF CircuitID = BTWD206969 THEN
    RETURN( NULL )
ELSE
    IF SuspendDate = CLOS or ENGC THEN
        IF SCD > BG1 THEN
            IF OrderType = QCS924 THEN
                RETURN( Value Looked-up from OrderValidatedTime2 10G Apollo LD )
            ELSE
                RETURN( NULL )
            ENDIF
        ELSE
            RETURN( NULL )
        ENDIF
ELSE
    RETRUN( NULL )
ENDIF

Now we can see that in even plainer English, the logic is:

If the CircuitID is NOT BTWD206969, then:
    If SCD > B1 *and* Order Type = QCS925, then do the lookup.
    
In all other cases return NULL.