Formula to generate a weighted average score


I'm trying to create a formula that will generate a weighted sum total across a couple of criteria columns after each column has been assigned a score.

Each of the columns will have a drop down where we can select a score (from 1 - 10). Each column will have a different weight:

Enterprise Priority: 35%; HR Priority Score: 25%; Costs Savings Score: 25%; HR Modernization: 15%

I would like the weighted score to be populated in the "HR Leadership Score" column.

How I would normally do this in Excel is not working at all, so any assistance would be appreciated!


  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 11/07/23

    @Emily Miller The AVGW formula should work. This will take your values and give them an Average Weight.

  • @JamesB - I keep getting Unparseable whenever I use that function. This is how I currently have the formula set up:

    =AVGW(([Enterprise Priority]@row, 0.35),([HR Priority Score]@row,.25),([Costs Savings Score]@row,.25),([HR Modernization]@row.15)

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Emily Miller

    Do you want the average weight of the percentages based on Score or just the average of the percentages. This will determine if you use AVG or AVGW.

    If just the average of the percentages then use

    =AVG([Enterprise Priority]@row,[HR Priority Score]@row,[Costs Savings Score]@row,[HR Modernization]@row)

    I hope this helps.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!