AverageIf not returning correct value

Question

Hi all. I am working with a sheet trying to get what - I thought - would be a simple average, but the formula seems to be returning the incorrect value. I am trying to get an average of the temperature in a column if the created date (created date is the Smartsheet default column) is equal to yesterday:

Which I would expect to be 271.34 based on the footnote stats that come up, but it isn't.

My guess is I am having a timezone issue, because the displayed average only runs up through 6pm, and I am in US EST. But I am not sure how to fix the issue. Any thoughts?

Paul Newcome · Accepted Answer

You will need a date type helper column with a formula to output the actual date based on the time zone difference. The first thing to do is find out what time the dates shift on the back-end. To do this, temporarily use

=DATEONLY(Created@row)

Once you figure out the time (I will use 9:00 PM in this example), you would use this formula:

=DATEONLY(Created@row) - IF(TIME(RIGHT(Created@row, LEN(Created@row) - FIND(" ", Created@row))) >= TIME("9:00 PM"), 1, 0)

Then you can run your AVERAGEIF off of this helper column.

Paul Newcome · Accepted Answer

Happy to help. 👍️

Both formulas should function just fine including with daylight savings time.

I have been leaning more towards the one I provided leveraging the time though simply because it is easier for me to see what is going on at a glance, but that's more a personal preference thing.