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

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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!