Help with quarter and YTD function - reference sheet error

Kristi Grenda

Hi Folks!

I'm working on creating reports for an audit review process, and I'm having some difficulty with getting the report per quarter and YTD formulas to function.

Here is the issue that I'm having. I need the @cell part to reference the Date of Encounter on my data sheet. However, when I try to do that, it gives me an error that reads that is a required field (the Date of Encounter is required on the audit form), and it won't let me add it. The formula is reading as invalid because I can't pull the date. The first part works fine (bringing the average over - SUMIF won't work because the audit scores are presented as % and 197% is really not an accurate reflection of the audit scores in any given time frame.

=AVG({Provider Audit - Combined Encounters - For Range 1}, [Primary Column]@row, {Provider Audit - Combined Encounters - For Range 2}, AND(@cell >= DATE(YEAR(TODAY()), 1, 1), @cell < DATE(YEAR(TODAY()), 4, 1)))

I'm new to Smartsheets, and I was unable to find any workaround or answers as to why required fields wouldn't be able to be added to a table sheet. Or if my formula is the issue. Any help would be appreciated.

Thanks!

Kristi Grenda

Jeff Reisman

@Kristi Grenda

Your quarters run January-March, April-June, July-September, October-December, yes? With your date criteria, it looks like you're only getting April and May for Q2, and July and August for Q3. Without seeing your data, I'm not sure if this would affect anything, but something to check.

Change your second Month criteria to <= 6 for Q2 and <= 9 for Q3.

Then if you're still not getting any results for Q3, remove the IFERROR structure and see what error message you get, if any. Could be a non-date value in the column, or blanks in the dataset. As a best practice, I don't add IFERRORs until after I confirm the formula does indeed work as it should; otherwise it's tough to troubleshoot (are there just no results, or is it replacing the error message with a blank space??)

If there are going to be blank cells in the range you're evaluating for a dates, you'll want to add a standalone criterion after the "Initial/Annual Visit" and before the {Date of Encounter} in your formula. This criterion will also be for {Date of Encounter} range, but it will make the formula exclude any non-date values and thus avoid potential errors:

=IFERROR(AVG(COLLECT({Audit Score %}, {Type of Encounter}, "Initial/Annual Visit", {Date of Encounter}, ISDATE(@cell), {Date of Encounter}, AND(MONTH(@cell) > 3, MONTH(@cell) < 6))), "")

This works because the formula is evaluating the criteria from left to right, reducing its subset of rows to consider as it gets to each criteria. So first it excludes rows with the wrong type of encounter, then of the remaining rows, it excludes the ones where Date of Encounter is not a date value, so then it's only going to collect the Audit Score % from the remaining rows that fall in the correct months.

Jeff Reisman

@Kristi Grenda

I believe it's saying you need to have a value in the "Sheet reference name" field when you are creating the reference. It has to be able to call it something 😀

Kristi Grenda

Thanks, Jeff.

That made sense. I totally missed that it didn't grab the sheet name. Thank you.

I have a new issue now. With the sheet name added, I get "unparsable" as an error. So, I tried adding the [Column3]@row to see if that would help (see below). That gives me a "Circular Logic" error.

=AVG({Provider Audit - Combined Encounters - For Range 1}, [Primary Column]@row, {Provider Audit - Combined Encounters - For Range 2}, AND({Provider Audit - Combined Encounters} >= DATE(YEAR(TODAY()), 1, 1), {Provider Audit - Combined Encounters - For Range 3} < DATE(YEAR(TODAY()), 4, 1, [Column3]@row)))

Any other suggestions on what I'm doing wrong here?

Thank you!

Kristi

Jeff Reisman

@Kristi Grenda It's hard to figure out without knowing what data you are referencing. I have no idea what is in [Column3]@row or how you thought that might help, but...

it looks to me like you're trying to do more than the AVG function can do - by trying to evaluate ranges for cells that meet given criteria. There IS a function that does this, AVERAGEIF. The problem is that AVERAGEIF can only do a single set of criteria; there's no AVERAGEIFS function to handle multiples.

In order to get an average when you need to evaluate multiple ranges and criteria, the surest way is to use a SUMIFS formula with your ranges/criteria to obtain your total amount, and divide that number by the result of a COUNTIFS formula with the same ranges/criteria.

So let's try to get you a solid set of SUMIFS and COUNTIFS.

Range 1 / Criteria 1: where {Provider Audit - Combined Encounters - For Range 1} equals [Primary Column]@row. Is this right?

Range 2 / Criteria 2: I'm not sure what you're looking for here. You list a range of {Provider Audit - Combined Encounters - For Range 2}, but then start an AND statement containing another two ranges as part of logical expressions. And then [Column3]@row is tacked on at the end inside a DATE function. Can you sort this out into sets of ranges and criteria?

Kristi Grenda

@Jeff Reisman Thank you so much for answering. OK. I have a screenshot of the updated pivot table and maybe better formulas. I have spent a lot of time watching and reading and experimenting today!

I am basically trying to group my data into Audit Score % for each visit type per quarter and then also YTD. I have figured out the YTD part. I also have learned how to create parent/child groups. This is the basic formula I have for the quarters pulling data from the primary sheet: =IFERROR(AVG(COLLECT({Audit Score %}, {Type of Encounter}, "Initial/Annual Visit", {Date of Encounter}, AND(MONTH(@cell) > 3, MONTH(@cell) < 6))), "")

I can adjust the name of the Encounter type and the Month range for the quarters. We don't have any actual data except for quarter 2, so it looks on the surface like it works. However, when I put in a sample Q3 data set, nothing changed on the data table for the Q3 formula of . =IFERROR(AVG(COLLECT({Audit Score %}, {Type of Encounter}, "Initial/Annual Visit", {Date of Encounter}, AND(MONTH(@cell) > 6, MONTH(@cell) < 9))), "")

Any ideas on where I might be going wrong? Or is that still not enough information?

Thanks again!

Kristi

Jeff Reisman

@Kristi Grenda

Your quarters run January-March, April-June, July-September, October-December, yes? With your date criteria, it looks like you're only getting April and May for Q2, and July and August for Q3. Without seeing your data, I'm not sure if this would affect anything, but something to check.

Change your second Month criteria to <= 6 for Q2 and <= 9 for Q3.

Then if you're still not getting any results for Q3, remove the IFERROR structure and see what error message you get, if any. Could be a non-date value in the column, or blanks in the dataset. As a best practice, I don't add IFERRORs until after I confirm the formula does indeed work as it should; otherwise it's tough to troubleshoot (are there just no results, or is it replacing the error message with a blank space??)

If there are going to be blank cells in the range you're evaluating for a dates, you'll want to add a standalone criterion after the "Initial/Annual Visit" and before the {Date of Encounter} in your formula. This criterion will also be for {Date of Encounter} range, but it will make the formula exclude any non-date values and thus avoid potential errors:

=IFERROR(AVG(COLLECT({Audit Score %}, {Type of Encounter}, "Initial/Annual Visit", {Date of Encounter}, ISDATE(@cell), {Date of Encounter}, AND(MONTH(@cell) > 3, MONTH(@cell) < 6))), "")

This works because the formula is evaluating the criteria from left to right, reducing its subset of rows to consider as it gets to each criteria. So first it excludes rows with the wrong type of encounter, then of the remaining rows, it excludes the ones where Date of Encounter is not a date value, so then it's only going to collect the Audit Score % from the remaining rows that fall in the correct months.

Kristi Grenda

That did it!! Thank you so much, Jeff!! I appreciate your help.

Kristi