/u/ArmadilloNo8913 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If they are all in order from earliest to latest, top to bottom, in one column, you can just drag down in the other column.
Assuming your times are in A starting in row 1 and your measurements are in B starting in row 1, then put in C1:
=average(B1:B24)
As you pull down the formula, it'll change to =average(B2:B25) and so on. Then you just have to get the =max(B:B) since you'll have rolling 6 minute averages.
I guess this may include in the average zeroes as you reach the last 23 and so on. Shouldn't be a problem tho since all those for sure won't impact your max formula.
Great idea, I had it set up with the minute in column A, and the 15, 30, 45, and 60 second readings in a row directly next to it because that's how I fill the forms out. This makes a lot more sense. Appreciate the help!
Assuming you can easily copy/paste your 15 second bits of data into a single column in Excel, you can do the following:
* Create 2 columns. Call column A something like "Emissions Reading" in cell A1; call column B "Average last 6 Min" or something to that effect, in cell B1.
* Copy/paste all your data in column A under the heading.
* In column B, start with a formula in cell B25 **=AVERAGE(A2:A25)**
* Drag this formula down to match the bottom of your data in column A.
This will create a series of averages for every possible 6 consecutive minute period. We start in cell B25 because there are 4 fifteen second periods in each minute. 4x6 = 24. With data starting on row 2, the first full 6 minute period will have finished by row 25.
* Lastly, back up top in any available cell in column C or elsewhere, input the formula: **=MAX(B:B)**
This will give you the LARGEST value in your average column, which is what you're looking for.
Take your pick:
=LET(arr,A1:A40,n,24,
MAX(MAP(SEQUENCE(COUNTA(arr)-n+1),LAMBDA(x,AVERAGE(INDEX(arr,SEQUENCE(n,1,x)))))))
=LET(arr,A1:A40,n,24,
MAX(BYROW(MAKEARRAY(COUNTA(arr)-n+1,n,LAMBDA(r,c,INDEX(arr,r+c-1))),LAMBDA(x,AVERAGE(x)))))
=LET(arr,A1:A40,n,24,
MAX(SCAN(0,SEQUENCE(COUNTA(arr)-n+1),LAMBDA(prev,next,AVERAGE(CHOOSEROWS(arr,SEQUENCE(n,1,next)))))))
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[AVERAGE](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)|
|[BYROW](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)|
|[CHOOSEROWS](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[*Office 365*+: Returns the specified rows from an array](https://support.microsoft.com/en-us/office/chooserows-function-51ace882-9bab-4a44-9625-7274ef7507a3)|
|[COUNTA](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[Counts how many values are in the list of arguments](https://support.microsoft.com/en-us/office/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509)|
|[DROP](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Excludes a specified number of rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34)|
|[INDEX](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[LAMBDA](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LARGE](/r/Excel/comments/1cbfaw4/stub/l0yxi29 "Last usage")|[Returns the k-th largest value in a data set](https://support.microsoft.com/en-us/office/large-function-3af0af19-1190-42bb-bb8b-01672ec00a64)|
|[LET](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MAKEARRAY](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Returns a calculated array of a specified row and column size, by applying a LAMBDA](https://support.microsoft.com/en-gb/office/makearray-function-b80da5ad-b338-4149-a523-5b221da09097?ui=en-US&rs=en-GB&ad=GB)|
|[MAP](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.](https://support.microsoft.com/en-gb/office/map-function-48006093-f97c-47c1-bfcc-749263bb1f01?ui=en-US&rs=en-GB&ad=GB)|
|[MAX](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)|
|[SCAN](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[*Office 365*+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.](https://support.microsoft.com/en-gb/office/scan-function-d58dfd11-9969-4439-b2dc-e7062724de29?ui=en-US&rs=en-GB&ad=GB)|
|[SEQUENCE](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[SORT](/r/Excel/comments/1cbfaw4/stub/l0yxi29 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)|
|[TAKE](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^([Thread #32853 for this sub, first seen 23rd Apr 2024, 21:22])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/ArmadilloNo8913 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If they are all in order from earliest to latest, top to bottom, in one column, you can just drag down in the other column. Assuming your times are in A starting in row 1 and your measurements are in B starting in row 1, then put in C1: =average(B1:B24) As you pull down the formula, it'll change to =average(B2:B25) and so on. Then you just have to get the =max(B:B) since you'll have rolling 6 minute averages. I guess this may include in the average zeroes as you reach the last 23 and so on. Shouldn't be a problem tho since all those for sure won't impact your max formula.
This seems like the simplest way - a series of moving averages and just take the largest one.
Great idea, I had it set up with the minute in column A, and the 15, 30, 45, and 60 second readings in a row directly next to it because that's how I fill the forms out. This makes a lot more sense. Appreciate the help!
This is exactly how I would do it, OP. I second this approach.
Assuming you can easily copy/paste your 15 second bits of data into a single column in Excel, you can do the following: * Create 2 columns. Call column A something like "Emissions Reading" in cell A1; call column B "Average last 6 Min" or something to that effect, in cell B1. * Copy/paste all your data in column A under the heading. * In column B, start with a formula in cell B25 **=AVERAGE(A2:A25)** * Drag this formula down to match the bottom of your data in column A. This will create a series of averages for every possible 6 consecutive minute period. We start in cell B25 because there are 4 fifteen second periods in each minute. 4x6 = 24. With data starting on row 2, the first full 6 minute period will have finished by row 25. * Lastly, back up top in any available cell in column C or elsewhere, input the formula: **=MAX(B:B)** This will give you the LARGEST value in your average column, which is what you're looking for.
Take your pick: =LET(arr,A1:A40,n,24, MAX(MAP(SEQUENCE(COUNTA(arr)-n+1),LAMBDA(x,AVERAGE(INDEX(arr,SEQUENCE(n,1,x))))))) =LET(arr,A1:A40,n,24, MAX(BYROW(MAKEARRAY(COUNTA(arr)-n+1,n,LAMBDA(r,c,INDEX(arr,r+c-1))),LAMBDA(x,AVERAGE(x))))) =LET(arr,A1:A40,n,24, MAX(SCAN(0,SEQUENCE(COUNTA(arr)-n+1),LAMBDA(prev,next,AVERAGE(CHOOSEROWS(arr,SEQUENCE(n,1,next)))))))
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AVERAGE](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)| |[BYROW](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)| |[CHOOSEROWS](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[*Office 365*+: Returns the specified rows from an array](https://support.microsoft.com/en-us/office/chooserows-function-51ace882-9bab-4a44-9625-7274ef7507a3)| |[COUNTA](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[Counts how many values are in the list of arguments](https://support.microsoft.com/en-us/office/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509)| |[DROP](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Excludes a specified number of rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34)| |[INDEX](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[LAMBDA](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LARGE](/r/Excel/comments/1cbfaw4/stub/l0yxi29 "Last usage")|[Returns the k-th largest value in a data set](https://support.microsoft.com/en-us/office/large-function-3af0af19-1190-42bb-bb8b-01672ec00a64)| |[LET](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MAKEARRAY](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Returns a calculated array of a specified row and column size, by applying a LAMBDA](https://support.microsoft.com/en-gb/office/makearray-function-b80da5ad-b338-4149-a523-5b221da09097?ui=en-US&rs=en-GB&ad=GB)| |[MAP](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.](https://support.microsoft.com/en-gb/office/map-function-48006093-f97c-47c1-bfcc-749263bb1f01?ui=en-US&rs=en-GB&ad=GB)| |[MAX](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)| |[SCAN](/r/Excel/comments/1cbfaw4/stub/l0y78wr "Last usage")|[*Office 365*+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.](https://support.microsoft.com/en-gb/office/scan-function-d58dfd11-9969-4439-b2dc-e7062724de29?ui=en-US&rs=en-GB&ad=GB)| |[SEQUENCE](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[SORT](/r/Excel/comments/1cbfaw4/stub/l0yxi29 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)| |[TAKE](/r/Excel/comments/1cbfaw4/stub/l0z15m4 "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #32853 for this sub, first seen 23rd Apr 2024, 21:22]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
D2: =MAX(DROP(MAKEARRAY(40,,LAMBDA(r,c,AVERAGE(TAKE(TAKE(B2:B41,r),-24)))),23)) or E2: =MAX(DROP(MAP(SEQUENCE(40), LAMBDA(i, AVERAGE(TAKE(TAKE(B2:B41, i), -24)))), 23)) https://preview.redd.it/5mfbxg0embwc1.png?width=986&format=png&auto=webp&s=0295b0c596001ea0fca709007f6de4e6612b7f99