AVERAGEIF Function

JSabillon
JSabillon ✭✭
in Formulas and Functions

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.

image.png

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

Thank you!

Jen

· Share on FacebookShare on Twitter
«1

Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

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

    · Share on FacebookShare on Twitter
  • JSabillon
    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"))

    · Share on FacebookShare on Twitter
  • 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

    · Share on FacebookShare on Twitter
  • JSabillon
    JSabillon ✭✭

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

    · Share on FacebookShare on Twitter
  • 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"))

    · Share on FacebookShare on Twitter
  • JSabillon
    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})

    · Share on FacebookShare on Twitter
  • 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.

    · Share on FacebookShare on Twitter
  • 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)))

    · Share on FacebookShare on Twitter
  • RaffyM
    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)

    · Share on FacebookShare on Twitter
  • 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)

    · Share on FacebookShare on Twitter
  • 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.

    · Share on FacebookShare on Twitter
  • 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))

    · Share on FacebookShare on Twitter
  • 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.

    · Share on FacebookShare on Twitter
  • 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

    · Share on FacebookShare on Twitter
  • 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.

    image.png


    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!