INCORRECT ARGUMENT SET

UChristi

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.

DKazatsky2

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

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

@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")

UChristi

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

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

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