Formula are not updating automatically

Matthew Occena

I have a sheet with a form which allows a user to enter a room and row number along with the collected data. I then wanted to average out the current weeks collected data but still have the historical data from previous collections. To isolate the current weeks data for my AVG(COLLECT formula I added a couple of helper columns.

Column Current Week

=Weeknumber(Today())

Column Week

=Weeknumber([Submission Time]@row)

Column Submission Time - this is a created date column for when the form was submitted

Current week Helper

=If([Current Week]@row >=Week@row, 1, 0)

I wanted the helper column to show 1 if that data is from the current week, but the issue is when I manually change the value of the Week Column to something lower than the current week value it does not update the helper column. Does anyone know how to fix that?

I have tried to reopen and refresh the sheet but it still does not update.

Matthew Occena

@Paul Newcome @Rose Howard Thank yall for your help I just talked to one of my team members and we were able to figure it out. I set up the logic of the Current week helper column incorrectly

Rose Howard

Matthew, depending on what you're doing with the data, that sounds like a Sheet Summary might work better? You can write formulas in the sheet summary with multiple parameters to check against and then average it?

Paul Newcome

Are you able to provide some screenshots for context?

Matthew Occena

@Rose Howard thank you for the suggestion but I don't think a Sheet Summary Report will work as the columns that contain information I wish to average are not system column, I tried to create a Sheet Summary Report and it will only let me import system columns like created and created by. I can create a Row report and Filter the information I want however when I apply the filter since the helper columns are not updating that will bring in outdated information into that report.

Rose Howard

@Matthew Occena Sorry for the confusion. I wasn't suggesting a Sheet Summary Report. Merely a Sheet Summary. They can be customized to lots of formulas:

Here's an example of how I'm using one to check against multiple options:

(To SUM total guests that meet first criteria then second criteria)

=SUMIFS([Total Guests including yourself]:[Total Guests including yourself], [Event Name]:[Event Name], "MAE Alumni Evening Event April 24", Title:Title, "MAE Advisory Council Member")

It's looking at a total count, then referencing two columns to get a sum. I believe you could do the same with Averages.

Matthew Occena

@Paul Newcome

This is the set up for the current week and current week helper column as mentioned before

and these are the columns for the form entries. I manually change the first row to 12 and the current week helper column shown above still has it as a current week. the second row is the formula i mentioned in the original post

I also have a blank helper column to filter out blank entries and that updates automatically when it shows 0 this indicting either room, row, count or rating are blank). When I remove like the thrips count it changes the blank helper to 0 instantly. but when I change the Week column to something lower than the Current week column it doesnt update the current week helper column and thats what I was asking for help on.

I just want to understand why the blank helper column updates with no issues but not the current week helper column

Matthew Occena

@Paul Newcome @Rose Howard Thank yall for your help I just talked to one of my team members and we were able to figure it out. I set up the logic of the Current week helper column incorrectly