Forum Discussion
Help for Excel
Hey everyone
Mabey someone can help me. I' ve been searching for a formula on Excel, that counts the average of 24 lines in a tabel with some tausends of data points. Selecting manualy 24 Lines every time costs alot of time. Mabey it is possible to automaticly generate this average data with some fomula or something?
Thanks for your helping answers
To average blocks of 24 entries you could use
= BYROW(WRAPROWS(data, 24), AVERAGEλ)where
AVERAGEλ = LAMBDA(x, AVERAGE(x))Depends on which Excel platform/version you and do you mean running average or average of each 24 lines block.
As variant
=LET( r, ROWS(data), n, UNIQUE( INT( (SEQUENCE(r)-1)/24)), MAP( n, LAMBDA(i, AVERAGE( INDEX(data, SEQUENCE( MIN(r-i*24, 24),,i*24+1) )) ) ) )Mabey someone can help me. I' ve been searching for a formula on Excel, that counts the average of 24 lines in a tabel with some tausends of data points. Selecting manualy 24 Lines every time costs alot of time. Mabey it is possible to automaticly generate this average data with some fomula or something?
It's definitely possible, although your description raises as more questions than a simple answer can respond to.
IF what you're trying to do is get the average of 24 rows in sequence, just a different set of 24 each time, a formula like the following will do it. Let's assume the numbers to be averaged are in column C, going all the way from C2 to C2000. If you put this formula in cell D25 and copy it down to D2000, it will show you the average of the 24 cells that include the cell in column C adjacent to the one in D containing the formula, averaged in with the 23 cells above.
See the attached spreadsheet.
IF that's not what you want--or some variation on that--then you'll need to be a LOT clearer in your request.
I have 8760 datas in one column. My target is to get over every 24 datas an average. But the seconde average (from 25-59 [24 datas]) has to be individual. In the End i have to count 365 generated datas.
You don't seem to realize what I gave you (or your desire still isn't clear).
The formula I gave you in cell D60 will be the averages from 25-59.
The formula I gave you in cell D84 will be the averages from 60-84,
and so forth, all the way down to Cell D8761 with the averages from 8736-8760
You weren't clear the first time as to which 24 cells were to be averaged, how they were to be selected. I was making an assumption that it could be what SergeiBaklan referred to as a "running average" of the most recent 24.
It's clear now that each set of 24 represents an hourly reading of something for one of the 365 days of a year. That wasn't clear before. The formula will work; I'm sure there are other ways to make it more elegant, but without knowing more about your spreadsheet with the raw data--how its arrayed or organized--it's hard to know how to help.
If you have a date as part of the row of data, then the AVERAGEIF function could produce the averages for any given day. Coupled with a table of days of the year, you could readily produce a column with one row per day and a cell that shows the average for the day. You need to give more information to get a more precise answer.
