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!

Elaine Balderrama

I am also getting #Incorrect argument error. help with this formula. I tried several times different ways to get a result. but no luck .. i am tryoing to get the average of the attributes.. scored as Yes= 1, N/a = 1 and No is 0 but specific columns would give an automatic zero if missed one or more attributes. Here's the formula..

=IF([Delivered expected outcome]@row = "No", 0, IF([Account Documentation]@row = "No", 0, IF([Payment compliance (PCI)]@row = "No", 0, IF([Maintained Data Integrity and Confidentiality]@row = "No", 0, IF([Transaction Free of Misbehavior & Unprofessionalis]@row = "No", 0, AVG(IF([Conversational & Professional]@row = "Yes", 1, IF([Conversational & Professional]@row = "N/A", 1, 0)), IF([Engagement & Personalization]@row = "Yes", 1, IF([Engagement & Personalization]@row = "N/A", 1, 0)), IF([Tone and Pace]@row = "Yes", 1, IF([Tone and Pace]@row = "N/A", 1, 0)), IF(Language@row = "Yes", 1, IF(Language@row = "N/A", 1, 0)), IF([Delivered expected outcome]@row = "Yes", 1, IF([Delivered expected outcome]@row = "N/A", 1, 0)), IF([Exhibit Critical Thinking]@row = "Yes", 1, IF([Exhibit Critical Thinking]@row = "N/A", 1, 0)), IF([Educate and Accurately handle information]@row = "Yes", 1, IF([Educate and Accurately handle information]@row = "N/A", 1, 0)), [Effective use of resources]@row = "Yes", 1, IF([Effective use of resources]@row = "N/A", 1, 0)), IF([Case Control and Timeliness]@row = "Yes", 1, IF([Case Control and Timeliness]@row = "N/A", 1, 0)), IF([Account Documentation]@row = "Yes", 1, IF([Account Documentation]@row = "N/A", 1, 0)), IF([Payment compliance (PCI)]@row = "Yes", 1, IF([Payment compliance (PCI)]@row = "N/A", 1, 0)), IF([Maintained Data Integrity and Confidentiality]@row = "Yes", 1, IF([Maintained Data Integrity and Confidentiality]@row = "N/A", 1, 0)), IF([Transaction Free of Misbehavior & Unprofessionalis]@row = "Yes", 1, IF([Transaction Free of Misbehavior & Unprofessionalis]@row = "N/A", 1, 0)))))))