INCORRECT ARGUMENT SET

Options

Hi,

First time poster here. I see there is a great community of users here willing to offer their expertise. I am struggling with a formula and am hoping to get some help.

I would like to reference a different sheet to display an average score for a date range (by quarter) for each of our query authors. When using the below formula, I receive an error. I have tried to review other posts to see if I can troubleshoot on my own, but haven't had any luck. Hopefully one of you smart ones out there can spot the error right away. :-)

Please let me know if you need to see more images.

Tags:

Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @UChristi,

    You're syntax is just a little off, try this.

    =IFERROR(AVG(COLLECT({Query Audit Results - Score}, {Query Audit Results - Query Author}, =[Labels2]@row, {Query Audit Results - Recorded Date}, @cell >= DATE(2023, 7, 1), {Query Audit Results - Recorded Date}, @cell <= DATE(2023, 9, 31))), "")

    Hope this helps,

    Dave

Answers

  • UChristi
    Options

    Sorry, somehow I accidentally removed the formula I am using. Here it is:

    =IFERROR(AVG(COLLECT({Query Audit Results - Query Author}, Labels@row, {Query Audit Results - Score}, {Query Audit Results - Recorded Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 9, 31)))), "")

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @UChristi

    What are your date ranges for your quarters?

    The best way to pull the information is =SUMIFS()/CountIFS() to get your %. so it would look like

    =SUMIFS({Score ref}, {Date Ref}, >="beginningQuarter date", {Date Ref}, <= "End Quarter Date",{Name Ref},=Labels@row)/CountIFS({Name Ref},=Labels@row, {Date Ref}, >="beginningQuarter date", {Date Ref}, <= "End Quarter Date")

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • UChristi
    Options

    Thanks @Mark.poole. The date range I am using for the first quarter as shown in the formula in my post above is for the 7/1/23 - 9/31/23 (technically 9/30). I entered the date parameters using the format generated by the AI formula tool for the sheet summary, which works fine for that - I just can't seem to get it to work when referencing from another sheet. I will try and figure out your suggestion and see if i can get that to work. I am still somewhat new to smartsheet so it's lots of trial and error!

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @UChristi,

    You're syntax is just a little off, try this.

    =IFERROR(AVG(COLLECT({Query Audit Results - Score}, {Query Audit Results - Query Author}, =[Labels2]@row, {Query Audit Results - Recorded Date}, @cell >= DATE(2023, 7, 1), {Query Audit Results - Recorded Date}, @cell <= DATE(2023, 9, 31))), "")

    Hope this helps,

    Dave

  • UChristi
    Options

    @DKazatsky2, this worked perfectly! Thank you so much for taking the time to help me. Much apprecaited!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!