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**
Improve this question
8
  • 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
    Commented Feb 20, 2017 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
    Commented Feb 20, 2017 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
    Commented Feb 20, 2017 at 16:41
  • 1
    same list, same row
    – Kaylee Baugh
    Commented Feb 20, 2017 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
    Commented Feb 20, 2017 at 17:16

1 Answer 1

Reset to default
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"))

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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