myworld2016
07-08-2016, 10:18 AM
Hello,
Hope all are doing well.
I'm needing some assistance in solving this mystery involving nested IF statements.
It seems the formula I have currently (see very bottom) does not work with 'ALL' of the conditions.
I have a sample file attached for anyone's viewing.
Below are the conditions:
IF user chooses both GR303 (Protocol) & DMS - Legacy (Switch Type) then the following will occur:
I J K
BAY SHELF CIRCUIT
000 000 099 <= (last row of each slot) Every time counts to 99,then next cell on next slot will revert to 1.
000 001 001<=Once reverts to 1, then shelf will increase by +1.Shelf will remain at 1, until it increments up another 99 count.
IF user chooses both GR303 (Protocol) & DMS - Metaswitch (Switch Type) then the following will occur:
I J K
BAY SHELF CIRCUIT
000 000 999 <= (last row of each slot) Every time counts to 99, then next cell on next slot will revert to 1.
000 001 001<=Once reverts to 1, then shelf will increase by +1
Shelf will remain at 1, until it increments up another 999 count.
However, the code will need to also take in account if user DOES NOT choose any of the two DMS switches, then:
GR303 -Last cell col. K +2
MGCP - Col K, Then next cell is 1
GR303
I J K
BAY SHELF CIRCUIT
000 000 024 <= (last row of each slot) Every time counts to 99, then next cell on next slot will revert to 1.
000 001 026<= Then shelf will increase by +1.
Shelf will remain at 1, until it meet at next slot then increase +1
MGCP
I J K
BAY SHELF CIRCUIT
000 000 024 <= (last row of each slot) Every time counts to 99, then next cell on next slot will revert to 1.
000 001 001<= Circuit will revert back to 1
The formula I currently have in J
=IF(AND(SWITCH_TYPE="DMS - Legacy",N240>=99),J240+1,IF(OR(SWITCH_TYPE="DMS - Metaswitch",N240>=999),J240+1,IF(AND(PROT_MAIN<>"GR303"),J240+1,IF(AND(PROT_MAIN="GR303",SWITCH_TYPE=SWITCH_TYPE),0,J241+2))))
The formula I currently have in K
=IF(AND(K240>=99,SWITCH_TYPE="DMS - Legacy",SKIP_PN_YN="YES"),1,IF(AND(K240>=999,SWITCH_TYPE="DMS - Metswitch",SKIP_PN_YN="YES"),1,IF(PROT_MAIN<>"GR303",1,K240+2)))
Values are inputted from the "MAIN" worksheet and then transposed to either the 5ESS or Standard. According to what the user chooses.
Thank you for any support provided.
Myworld2016
Hope all are doing well.
I'm needing some assistance in solving this mystery involving nested IF statements.
It seems the formula I have currently (see very bottom) does not work with 'ALL' of the conditions.
I have a sample file attached for anyone's viewing.
Below are the conditions:
IF user chooses both GR303 (Protocol) & DMS - Legacy (Switch Type) then the following will occur:
I J K
BAY SHELF CIRCUIT
000 000 099 <= (last row of each slot) Every time counts to 99,then next cell on next slot will revert to 1.
000 001 001<=Once reverts to 1, then shelf will increase by +1.Shelf will remain at 1, until it increments up another 99 count.
IF user chooses both GR303 (Protocol) & DMS - Metaswitch (Switch Type) then the following will occur:
I J K
BAY SHELF CIRCUIT
000 000 999 <= (last row of each slot) Every time counts to 99, then next cell on next slot will revert to 1.
000 001 001<=Once reverts to 1, then shelf will increase by +1
Shelf will remain at 1, until it increments up another 999 count.
However, the code will need to also take in account if user DOES NOT choose any of the two DMS switches, then:
GR303 -Last cell col. K +2
MGCP - Col K, Then next cell is 1
GR303
I J K
BAY SHELF CIRCUIT
000 000 024 <= (last row of each slot) Every time counts to 99, then next cell on next slot will revert to 1.
000 001 026<= Then shelf will increase by +1.
Shelf will remain at 1, until it meet at next slot then increase +1
MGCP
I J K
BAY SHELF CIRCUIT
000 000 024 <= (last row of each slot) Every time counts to 99, then next cell on next slot will revert to 1.
000 001 001<= Circuit will revert back to 1
The formula I currently have in J
=IF(AND(SWITCH_TYPE="DMS - Legacy",N240>=99),J240+1,IF(OR(SWITCH_TYPE="DMS - Metaswitch",N240>=999),J240+1,IF(AND(PROT_MAIN<>"GR303"),J240+1,IF(AND(PROT_MAIN="GR303",SWITCH_TYPE=SWITCH_TYPE),0,J241+2))))
The formula I currently have in K
=IF(AND(K240>=99,SWITCH_TYPE="DMS - Legacy",SKIP_PN_YN="YES"),1,IF(AND(K240>=999,SWITCH_TYPE="DMS - Metswitch",SKIP_PN_YN="YES"),1,IF(PROT_MAIN<>"GR303",1,K240+2)))
Values are inputted from the "MAIN" worksheet and then transposed to either the 5ESS or Standard. According to what the user chooses.
Thank you for any support provided.
Myworld2016