2

I am using SharePoint 2007 and trying to automate a couple of columns using calculated value. I have three columns that you enter a number and in the 4th column it totals the average (this works fine). However, I am trying to add a 5th column that a assigns a ranking based on the average score and can't get it to work. If I put the formula in Excel it works perfectly, but not in SharePoint. What am I doing wrong?

Option A

=IF(
    AVERAGE([Call 1 Score %],[Call 2 Score %],[Call 3 Score %])<85
    ,"DO"
    ,IF(
        AVERAGE([Call 1 Score %],[Call 2 Score %],[Call 3 Score %])<93
        ,"SP"
        ,"RM"
       )
   )

Option B

=IF(
    Combined Score Avg<85
    ,"DO"
    ,IF(
        Combui<93
        ,"SP"
        ,IF(
            B2>92.9
            ,"RM"

           )
       )
   )

Example

The bolded example column is where the trouble is.

Call1     Call2     Call3       Avg      Rating
85        92          78        85        **SP**
  • In Excel you can access other rows and apply formula on them, in SharePoint Calculated Columns you can't access other rows / list items and apply formula on them. You can only use fields of same row / list item. – Arsalan Adam Khatri Feb 20 '17 at 16:37
  • Correct these are the same row. Essentially, column 1, 2, and 3 of the row need to be averaged in column 4 (already works), then column 5 needs to take the data and assign a rating. – Kaylee Baugh Feb 20 '17 at 16:40
  • column 5 needs to take the data which data? if that means data from other list items, it's not possible via calculated column only – Arsalan Adam Khatri Feb 20 '17 at 16:41
  • 1
    same list, same row – Kaylee Baugh Feb 20 '17 at 16:46
  • You mean you have a third Formula Rating? What happens if you name it anything but Rating? In SP2010 that became a special SP column, maybe it was already there in 2007 – Danny '365CSI' Engelman Feb 20 '17 at 17:16
0

I was able to find out what the issue was. From a technical standpoint the formulas are correct, but because the columns that the formula was pulling from were % columns, the formula needed to include %. I ended up using the simplified version. It is below for reference. Thanks everyone for the assistance.

=IF([Combined Score} Avg<85%,"DO",IF([Combined Score Avg]<93%,"SP","RM"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.