# Thread: Calculated Field – function help IF X OR Y THEN =1

1. ## Calculated Field – function help IF X OR Y THEN =1

I have a spreadsheet with a table in it. The column titled “Level” is either A, B, C, D, or E values for each row. I also have a “Training” and a “Certification” column with values for VVV, XXX, YYY, or ZZZ.

1. Create a field “Cal-Level” to calculate a value for the “Level” where:
IF “Level” = A then “Cal-Level” = 1
IF “Level” = B or D then “Cal-Level” = 2
IF “Level” = C or E then “Cal-Level” = 3

2. Create a field “Cal-Training” to calculate a value for the “Training” where:
IF “Training” = VVV or XXX then “Cal-Training” = 1
IF “Training” = YYY then “Cal-Training” = 2
IF “Training” = ZZZ then “Cal-Training” = 3
*I will be repeating the above to create “Cal-Certification”

3. Create a field for “Training Met” and “Cert Met”
IF “Cal-Level” = or < “Cal-Training” THEN “Training Met” = TRUE
IF “Cal-Level” = or < “Cal-Certification” THEN “Cert Met” = TRUE

I have attempted a couple combinations, but haven’t come up with anything that works. Any help is greatly appreciated.
Also, if anyone knows if this is drastically different when used in a SharePoint list it would be helpful for transitioning it over.
Thank you.

2. Is the attached something like what you are after? I have created a lookup range for the "level" and the "training/Cert" values.

Steve

3. ## Calculated Field - formula contained in field for transition to SharePoint

Steve,

Your results are as I need but the method will not work when transferred to SharePoint because the Cal-Fields would need to contain the entire formula to function. The attached TAB 2 is how the data is actually stored. I listed what the formula is calculating at the bottom. Hopefully this better describes what I am doing.

4. These seem to match your values. Enter the formulas in row 2 and copy down the column. [Note you list for "Cert Met" that: "IF [Cal-Training] is > or = to [CAL-Level] THEN [Training Met] = TRUE" which is exactly the same thing you have for "Training Met". I presume you meant "IF [Cal-Certification] is > or = to [CAL-Level] THEN [Training Met] = TRUE"]
C2: =IF(B2="A",1,IF(OR(B2="B",B2="D"),2,IF(OR(B2="C",B 2="E"),3,"na")))
L2: =IF(J2,3,IF(H2,2,IF(OR(F2,D2),1,0)))
M2: =IF(K2<>"",3,IF(I2<>"",2,IF(OR(G2<>"",E2<>""),1,0) ))
N2: =L2>=C2
O2: =M2>=C2

Steve