AVERAGEIF Function

JSabillon

Hi all -

I am attempting to collect the average of a column of scores IF criteria is met in a separate column. For example:

In the image below, I want to get the average of the Flexibility Score Column IF the Project - Client Name column has a specific name.

I tried the suggestions in previous posts but continue to get #UNPARSABLE or #INVALID

Thank you!

Jen

Hollie Green

=AVG(Collect([Flexibility Total]:[Flexibility Total],[Project-Client Name]:[Project-Client Name],"specific name criteria"))

JSabillon

@Hollie Green - Thank you for sending this along! I tried this with my criteria and it came back #UNPARSEABLE. Here's exactly what I tried:

=AVG(Collect([Flexibility Total]:[Flexibility Total],[Project - Client Name]:[Project - Client Name],"University of Imagination"))

Hollie Green

I created a test sheet and it works on my sheet I even copied your formula and pasted once I had the columns created and data put in. What are your column types? Also where are you putting the formula is it on the same sheet, a different sheet, or in the Summary?

@JSabillon

JSabillon

@Hollie Green - the formula is on a metrics sheet (separate). I wonder what I'm doing incorrectly??

Hollie Green

That is what is incorrect if it is on a different sheet you need to create your cross sheet references and you would replace with your reference names.

=AVG(COLLECT({Flexibility Total Reference Name},{Project - Client Reference Name},"University of Imagination"))

JSabillon

Thanks, Hollie! I actually got it to work with this one:

(actual formula on the metrics sheet):

=AVERAGEIF({Project Team Climate Survey Range 80}, "University of Imagination", {Project Team Climate Survey Range 82})

(general formula on the metrics sheet):

=AVERAGEIF({Project - Client Name Column}, "Client Name", {Flexibility Total Column})

RaffyM

Hello @Hollie Green

I have similar formula and it works but my difficulty was if I have two conditions, it gives me incorrect argument result. This is my formula, could you help check and advise.

=AVERAGEIF({Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)), {Test 2023 Total Cost}) / 2)

Thank you in advance.

Hollie Green

If I am understanding correctly you want the Average of the Test 2023 Total Cost if the Test 2023 Duplicate value is 1 and the Test 2023 Received date is either greater than 1/1/23 or less than 3/31/23. . The below formula should work once your references are created and if I haven't made any typos. Let me know if it works.

=AVG(COLLECT({Test 2023 Total Cost},{Test 2023 Duplicate},1,{Test 2023 Receive Date},AND(@cell>=Date(2023,1,1),@cell<=Date(2023,3,31)))

RaffyM

https://community.smartsheet.com/discussion/comment/380364#Comment_380364

Hi @Hollie Green, your understanding is correct. I copied the formula and added to my existing equation, it works in one column. However if I changed the date range or another reference column, it gives me result of #divided by zero.

This one works: =ROUNDUP(AVERAGEIF({Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)), {Test 2023 Days Delayed}) - AVG(COLLECT({Test 2023 Days Delayed}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))) / 2)

This gives me error: =ROUNDUP(AVERAGEIF({Test 2023 Receive Date}, AND(@cell >= DATE(2023, 4, 1), @cell <= DATE(2023, 6, 30)), {Test 2023 Days Delayed}) - AVG(COLLECT({Test 2023 Days Delayed}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 4, 1), @cell <= DATE(2023, 6, 30)))) / 2)

This one too, gives me error: =ROUNDUP(AVERAGEIF({Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)), {Test 2023 Days 3F+P Completion}) - AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))) / 2)

Hollie Green

If you are just trying to get your average that does not include any duplicates I would change the formula to the below if you have your duplicate column set to return a value of 1 or 0. If that isn't what you are trying to do then I would suggest adding an iferror formula to show a result of "" when the error returns for that portion of the formula.

=ROUNDUP(AVG(COLLECT({Test 2023 Total Cost},{Test 2023 Duplicate},0,{Test 2023 Receive Date},AND(@cell>=Date(2023,1,1),@cell<=Date(2023,3,31)))

Formula adding iferror

=ROUNDUP(AVERAGEIF({Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)), {Test 2023 Days 3F+P Completion}) - IFERROR(AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31))),"")) / 2)

RaffyM

@Hollie Green I would like to get the average including only one of the duplicate data that's why I added division function (divided by 2) in the second equation: AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))) / 2)

I copied and tried the IFERROR formula above but it gives me incorrect argument set result.

Hollie Green

Try an If statement to go with it that way you can exclude the duplicates if there are not any duplicates.

=IF(INDEX(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31),1)<1,AVG(COLLECT({Test 2023 Days 3F+P Completion},{Test 2023 Receive Date},AND(@cell>=DATE(2023,1,1),@cell<=DATE(2023,3,31)),AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))) / 2))

RaffyM

Good morning @Hollie Green. Thank you for checking my concern and providing suggestions, really appreciate it.

I tried also the last formula unfortunately also giving me error result.

Hollie Green

What error message is it giving it's possible I have a typo, missing or extra ) or a comma somewhere

Also if you want to share the full set up I can do a test sheet and copy and paste directly from the test sheet

RaffyM

Good morning @Hollie Green, sorry for my late response.

I get incorrect argument set error using the latest formula.

Here is sniff of the sheet where I reference the columns.