understanding this excel formula

Question

=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?

Answer 1

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.