Results 1 to 4 of 4
  1. #1
    Star Lounger
    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 “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. #2
    WS Lounge VIP sdckapr's Avatar
    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.

    Steve
    Attached Files Attached Files
    Last edited by sdckapr; 2012-07-06 at 16:21.

  3. #3
    Star Lounger
    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 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.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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 [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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •