Calculating averages based on specific criteria per week.

Rachel Rudd

I have a smartsheet that I am trying to find an average rating per week if there is specific criteria in a column. I've condensed the columns I'm trying to find an average of below

The 'Created' column is an Auto-Number column for when the data is entered. I'd like to calculate the average, or 'Over All Rating' for each week that Zephyr is in the Lift Name column. I think I'm looking for a =AVG(COLLECT) formula, however I'm not sure what order to put them in. This formula is going into a new sheet and not the current sheet.

Kelly Moore

Hey @Rachel Rudd

I'm not sure if you sheet records (to keep) each week's average, or if the sheet is set up to reflects the current week's average. To do the former you would need a column on your destination sheet listing 1-52 for the weeknumbers of the year, date reference per week - like every Monday, or automation to move the row off the sheet.

In the formula below, I added an ISNUMBER to help filter any cells that shouldn't be included in the average. Also, remember with cross sheet references you must create each reference through the formula window - you cannot simply copy paste this formula. Your ranges will default to names different than I have listed - that is ok, and if you like, you can change those default names. In the second formula make sure you change the [WeekNumber] to match your column name or however your week is referenced.

Current week Only

=AVG(COLLECT({source sheet Over All Rating column}, {source sheet Over All Rating column}, ISNUMBER(@cell), {Source sheet Created column}, WEEKNUMBER(TODAY()) = WEEKNUMBER(@cell), {source sheet Left Name: column}, "Zephyr"))

Week by Week (assumes 1-52 on destination sheet)

=AVG(COLLECT({source sheet Over All Rating column}, {source sheet Over All Rating column}, ISNUMBER(@cell), {Source sheet Created column}, WEEKNUMBER(@cell)=[WeekNumber]@row, {source sheet Left Name: column}, "Zephyr"))

Does this get you what you need?

Kelly

Rachel Rudd

The 'Over All Rating' is the average rating for that day. I do not have the current weekly average anywhere, which is what I'm trying to pull into this sheet.

I am looking for it week by week and I tried the formula you offered but it comes back Unparseable.

Kelly Moore

Hey @Rachel Rudd

I may have misunderstood exactly what you need - it sounds like you need first the daily total on your source sheet, then you want to pull that into a weekly? Can you give me a screenshot of your current formula (a shot of the formula with the colors) in the sheet this formula resides in?

Unpareseable errors usually come from column names (are all the column names (assuming you're on the same sheet as the formula) colored?), commas and parentheses (is the last parenthesis blue?). A screenshot gives me a lot of information

Rachel Rudd

I'm trying to transport from one sheet to a new one, so it's showing reference sheet range 1.What is giving me Unpareseable is the second formula you provided above.

Rachel Rudd

@Kelly Moore

I found this question which is similar to what I'm looking to do, however they are looking for a criteria by year and I'm looking for criteria by a weekly range.

https://community.smartsheet.com/discussion/74546/avg-of-a-range-with-2-criteria

Kelly Moore

Rachel,

As mentioned earlier, a screenshot of the data and the colored formula would be very helpful. I can make the formula very specific with that information. It's difficult trouble-shooting formulas sight unseen.

Using my 2nd formula - are you using weeknumbers to reference the specific weeks? This is what will pull in the data by week, similar to the YEAR function pulling in the year buckets of data in your linked post.

Did you change my column name to match yours? I included square brackets around my WeekNumber but as written, without a space between the words, the brackets are not needed. I assumed you would be changing that anyway to match your column.

Tell me what you have checked for the unparseable.

Kelly

Rachel Rudd

Above is the second formula you offered as I entered it to calculate the averages per week.

Safety Business Plan 21.22 Range 2 is Over All Rating Column

Safety Business plan 21.22. Range 3 is Created column, which are Auto-Number dates when submitted.

Safety Business plan 21.22 Range 1 is Lift Name, in this instance searching for Zephyr.

The dates in the week columns are the ones I'm trying to find averages for each lift for each week.

Kelly Moore

Hey Rachel

Thanks for the screenshot. How did you pull the week days into the Week Ranges column? Smartsheet looks at your Week ranges and doesn't see them as dates but as a long text string- like a sentence. Are the beginning and ending dates coming from separate columns and are these columns formatted as date columns? If yes, we can very easily use these in the formula. If no, no worries, I can work with the range and force it into a date value. Please let me know how the Week Ranges is obtained

Kelly

Kelly Moore

Hey Rachel

I haven't heard back. I will assume the date range text string is not linked to date cells. To compensate for that - add a helper column to your sheet - you can shove this column to the right and hide it after you add the column formula below. Name this column WeekNumber. This should be a text/number column.If you do not name it WeekNumber, adjust the Average formula above to match whatever you named this.

Add this formula to that helper column

=WEEKNUMBER(DATE(VALUE(MID([Week Ranges]@row, FIND("-", [Week Ranges]@row) - 5, 4)), VALUE(LEFT([Week Ranges]@row, FIND("/", [Week Ranges]@row) - 1)), VALUE(LEFT(RIGHT([Week Ranges]@row, LEN([Week Ranges]@row) - VALUE(FIND("/", [Week Ranges]@row))), FIND("/", RIGHT([Week Ranges]@row, LEN([Week Ranges]@row) - VALUE(FIND("/", [Week Ranges]@row)))) - 1))))

This converts the text string date range to something that the average formula (above) can compare against.

Kelly

Rachel Rudd

I was out last week for a personal matter. All columns are Text/Number and none are date columns. I pulled the weekdays into the column by typing out the weeks I wanted to pull the audits from.

I added the helper column and named it WeekNumber to the sheet I'm pulling the data to:

and I've left the formula in the Arrow column.

Kelly Moore

Hey Rachel

It appears the AVG formula is missing an ending parenthesis - the last parenthesis shown is pink instead of blue. It also appears you are missing a comma between the [WeekNumber]@row and {Safety Business Plan 21.22 Range 1}.

Kelly

Rachel Rudd

I've corrected the formula and now the outcome states 'Divide by Zero'. I believe this is for the ratings that don't have anything for the week range. Would there be a way to make them just say '0' or does it matter if I'm looking to graph this data aswell?

Kelly Moore

Yes, it would say dividing by zero for future dates.

Wrap your existing formula, parentheses and all, with an IFERROR(). You can make it say zero or leave it as a blank. I'll write it as a blank by using the double quotes. If you want zero, replace the double quotes with the zero.

=IFERROR(insert your existing formula here,"")

Rachel Rudd

It is pulling 'Divide by Zero' during previous weeks:

When I review where this data is coming from, there is no audit completed for the week of 1/15-1/21 for Backside, which is why I imagine it is pulling a 'Divide by Zero':