Excel formula to sum rolling 24 hours of data?

October 26, 2023 12:01 PM

I am trying to visualize a rolling average of how much formula my baby has drank over the previous 24 hours. Details inside!

I have 400 rows of data:
Column A: The date and time of the bottle.
Column B: The number of ounces in the bottle.
I want:
Column C: The sum of the ounces that have been fed over the 24 hour period prior to the current row (including that row if possible).

I'm NOT an excel power user. The best solution to my question will be a formula I can just drop in C2 and drag down to C401 and call it a day. Thank you!

posted by BuddhaInABucket to Grab Bag (10 answers total)

if you chart your data as a scatter plot, you should be able to right click a datapoint, choose "add trendline" then select a moving average with any period you want in the sidebar that pops up. no calculations required.
posted by Jobst at 12:18 PM on October 26, 2023 [1 favorite]

Assuming the data starts in row 2, and row 1 is headers, this should work for what you put in C2. (If not, replace all the instances of $2 with $1)
=SUMIFS(B$2:B2,A$2:A2,">="&A2-1)
The trick is that subtracting 1 from a date takes 24 hours off it, so this sums column B up to the current line where the corresponding value in column A is within 24 hours of the A in the current line.

Hopefully that makes sense.
posted by ambrosen at 12:20 PM on October 26, 2023

Response by poster: Jobst: The trendline gives me an average of the previous X number of feedings, but not the sum of the previous X number of hours.

Ambrosen- I inserted your formula (both with the $2 and $1) and.... nothing happens. The cell is empty.
posted by BuddhaInABucket at 12:32 PM on October 26, 2023

Response by poster: (for what it's worth: Column A is formatted as a Date, Column B and C as numbers)
posted by BuddhaInABucket at 12:33 PM on October 26, 2023

BiAB, can you try clearing the formatting for the cell with ambrosen's formula? If you copied and pasted directly into the cell, you may have copied the white colored text from the default Ask Mefi stylesheet.
posted by zamboni at 1:08 PM on October 26, 2023

Is the most recent row at the top or the bottom of the page?
posted by mskyle at 1:55 PM on October 26, 2023

Response by poster: OK, I'm making headway on why it's not working... For some reason the data exported the date/time in two formats, like this:

9/11/23 0:05
9/11/23 4:11
9/11/23 7:57
9/11/23 10:12
09/11/2023 13:35 PM
09/11/2023 19:10 PM
09/11/2023 21:07 PM
09/11/2023 21:09 PM

The formula works on the first type of date format, but not the latter. When I select the entire column and try to format it the same way, however, the latter doesn't change- it just stays the format it is. Weirdly, it's only the PM data that does this, and not the AM data. I think what I'm looking at is a messed up output from the app I use.
posted by BuddhaInABucket at 3:01 PM on October 26, 2023

Response by poster: I used text to columns so that now I have three columns that have consistent formatting.
Column A: Date
Column B: Time
Column C: Amount

What formula can I put in Column D to get the outcome I want?
posted by BuddhaInABucket at 3:08 PM on October 26, 2023

Response by poster: Oh, astonishing, I finally did it.
- I used text to columns to separate the date and time that were formatted inconsistently.
- After fixing the formatting, I used the DATE and TIME functions to refer to those cells into a new column, creating a column with consistently formatted date and time.
- I used Ambrosen's formula, adjusted to point to the column where my corrected date and time were.

Result: I now have the information I need!

A lot of time spent on something I was idly curious about. But there we have it.
posted by BuddhaInABucket at 3:15 PM on October 26, 2023 [2 favorites]

In the future, this is one of the types of problems that ChatGPT is actually quite good at. It can still sometimes spit back totally wrong ideas, and you have to cajole it and massage it. But I've succeeded in getting helpful answers regarding Excel/Google Sheets questions a number of times.
posted by Conrad Cornelius o'Donald o'Dell at 5:58 PM on October 26, 2023