Formula to return the average of points assigned to a specific person within a specific date range

Haley C.

Hi all, I have a fairly complex formula I'm tying to create, and can't manage to make it work.

Let's say my data sheet has three columns, representing a point value given to an individual on a specific date:

• Person (Number/Text, each cell contains one name)
• Points (Number/Text, each cell contains one number)
• Date (Date, each cell contains one date)

In my summary sheet, I want a formula that will return the average of the points given to an individual between two dates (the first and last of a given month). The closest I've gotten is returning the average points from ALL dates using the following formula:

=AVERAGEIF({Data Sheet Range 5}, "Firstname Lastname", {Data Sheet Range 3})

where "Data Sheet Range 5" is the Person column and "Data Sheet Range 3" is the Points column.

If you feel like responding, it would be extremely helpful if you use the exact punctuation marks, syntax, and structure I'll need for the formula (with the understanding that variables like ranges will be unique to my sheets). In some responses it seems like folks use shorthand such as [DATE] which gets really confusing for a novice! Thanks in advance!

Darren Mullen

@Haley C. Since AVERAGEIF only allows for one criterion range, you'll need to use a combination of the AVG() and COLLECT() functions.

COLLECT() will allow you to collect the points that meet the criteria of a date range and the person's name. Then you'd wrap COLLECT() in the AVG() to get the Average.

AVG(COLLECT(......your range and criterion ranges as needed.....))

Haley C.

@Darren Mullen Awesome, thank you so much! If you have a moment, could you write out a sample formula that might work for me? I've been really struggling to format the date ranges correctly within the other functions. I've read through a lot of instructions but I'm sure I'm still getting it wrong.

Darren Mullen

@Haley C. AVG(COLLECT({Points Range}, {Name Range}, "Name", {Date Range}, Month(@cell) = monthnumber ))

The month(@cell) function will get the month as a number (1 - 12) and you can set it equal to the month you want (replace monthnumber in the formula with the month you want). This is easier than dealing with a date range since you want the whole month.

Haley C.

@Darren Mullen Fantastic, thank you! Unfortunately I think I do need a date range because the data sheet covers dates that span multiple years.

So I think it'd be something like this:

=AVG(COLLECT({Name Range}, "Name", {Points Range}), AND({Date Range}@cell >=[12/1/22], {Date Range}@cell <=[12/31/22]))

based on some info I'm cobbling together between this discussion and https://community.smartsheet.com/discussion/43021/how-to-count-number-of-cells-between-two-dates-from-another-sheet

My formula doesn't work (getting the #UNPARSEABLE error) but I think it's close?

Darren Mullen

@Haley C. You need to use the DATE() function to create your dates. Alternatively you could use a combo of MONTH() and YEAR() to compare the month and year.

Also, you can't wrap ranges into an AND in the collect function like that. =AVG(COLLECT({Name Range}, "Name", {Points Range}), {Date Range},@cell >=DATE(), {Date Range},@cell <=DATE()))

Haley C.

@Darren Mullen Thank you so much for all your help! I plugged everything in exactly as you suggested, but I am still getting an #UNPARSEABLE error. Even broke it down to just =AVG(COLLECT({Name Range}), "Name", {Points Range}) to see if it might return an average, but that gave me an #INCORRECT ARGUMENT error.

Darren Mullen

@Haley C. remove the closing parenthesis after {Name Range} and add another to the very end of the formula.

Genevieve P.

Hi @Haley C.

I see you marked Darren's response as not helping - can you let us know what happened when you moved the closing parentheses to the end? You'll also want to ensure the column you want to Average is the first thing listed, like so:

=AVG(COLLECT({Points Range}, {Name Range}, "Name"))

If this hasn't helped, screen captures would be useful, but please block out sensitive data.

Thanks!

Genevieve