AVERAGEIF Function

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

«1

Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

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

  • @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
    Hollie Green ✭✭✭✭✭✭
    edited 05/24/23

    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

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

  • Hollie Green
    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"))

  • 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
    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
    Hollie Green ✭✭✭✭✭✭
    edited 06/13/23

    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
    RaffyM ✭✭✭✭

    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
    Hollie Green ✭✭✭✭✭✭
    edited 06/13/23

    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
    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
    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
    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
    Hollie Green ✭✭✭✭✭✭
    edited 06/14/23

    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
    RaffyM ✭✭✭✭
    edited 06/15/23

    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!