Results 1 to 4 of 4

20120706, 14:16 #1
 Join Date
 Jul 2007
 Location
 North Carolina, USA
 Posts
 71
 Thanks
 5
 Thanked 0 Times in 0 Posts
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 “CalLevel” to calculate a value for the “Level” where:
IF “Level” = A then “CalLevel” = 1
IF “Level” = B or D then “CalLevel” = 2
IF “Level” = C or E then “CalLevel” = 3
2. Create a field “CalTraining” to calculate a value for the “Training” where:
IF “Training” = VVV or XXX then “CalTraining” = 1
IF “Training” = YYY then “CalTraining” = 2
IF “Training” = ZZZ then “CalTraining” = 3
*I will be repeating the above to create “CalCertification”
3. Create a field for “Training Met” and “Cert Met”
IF “CalLevel” = or < “CalTraining” THEN “Training Met” = TRUE
IF “CalLevel” = or < “CalCertification” 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.

20120706, 14:36 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Is the attached something like what you are after? I have created a lookup range for the "level" and the "training/Cert" values.
SteveLast edited by sdckapr; 20120706 at 16:21.

20120713, 11:51 #3
 Join Date
 Jul 2007
 Location
 North Carolina, USA
 Posts
 71
 Thanks
 5
 Thanked 0 Times in 0 Posts
Calculated Field  formula contained in field for transition to SharePoint
Steve,
Thank you for your response.
To answer your question, Yes and No.
Your results are as I need but the method will not work when transferred to SharePoint because the CalFields 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.

20120714, 05:54 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 [CalTraining] is > or = to [CALLevel] THEN [Training Met] = TRUE" which is exactly the same thing you have for "Training Met". I presume you meant "IF [CalCertification] is > or = to [CALLevel] 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