Trying to average values from one column based on conditions of two other columns.

Dave from JBM

Where I have Columns "Employee Name", "Score Week", and "Week Score" I want to average the "Week Score" for the "Employee Name" where "Week Score" is a value of "1", "2", "3", or "4".

I started here:

=AVG(COLLECT([Week Score]:[Week Score], [Employee Name]:[Employee Name], [Employee Name]@row, [Score Week]:[Score Week], OR([Score Week]@row = "1", [Score Week]@row = "2", [Score Week]@row = "3", [Score Week]@row = "4")))

It is returning a value sometimes but doesn't seem to be correct. For example, Where there is three entries for the same employee listed as weeks 1, 3, and 4 with score values of 2.57, 2.57, and 2.3, its returning a value of 2.57 as it if its not averaging and instead just grabbing the first value it finds in that row that meets the criteria. Thanks in advance!

Kelly Moore

Hey @Dave from JBM

Try removing your numbers from quotes. You forced the numbers to behave as a text string when you added the quotes. Sometimes smartsheet will get confused when trying to match numerical values and text strings.

Did this work?

Kelly

Dave from JBM

@Kelly Moore That did not work unfortunately but thank you for the suggestion!

Kelly Moore

Hey Dave

Is it possible to share a screenshot of your data? It would be helpful to see how your data is structured.

Here's another thought

=AVG(VALUE(COLLECT([Week Score]:[Week Score], [Employee Name]:[Employee Name], [Employee Name]@row, [Score Week]:[Score Week], <=4)))

Kelly