Forum Discussion
Sorting out data from a column
Hi all,
I have a very long column (28281 rows) with dates and times (format: dd/mm/yyyy hh;mm). There are no breaks between days, weeks or months: data is continuous. Next two columns are numbers (wind speeds, min. and max). Each day has hourly data, i. e. each day has 24 rows. Data is for three years, with some missing months mainly in the beginning. I would like to:
a) Insert an empty line between each month, in order to calculate averages and medians per month;
b) Delete lines that refer to hours between 20;00 and 08;00 each day. I only need daylight data.
Would someone help me finding a formula to do this? I have tried doing it manually, but it is very very long.
Thanks in advance,
Luis Serpa
Luis Serpa I suggest you look into Power Query as demonstrated in the attached example. In general it's not a good idea to insert empty rows in data sets to hold calculations relating to sub-sets of the data. In your case averages and medians per month.
8 Replies
First of all, based on the text, I can recommend inserting the columns into a table and using filters to display the data as desired.
For more and precise information, please add more information.
Which operating system?
Which Office version?
Where are the files stored?
(OneDrive, Sharepoint, hard drive, USB, etc.).
Insert the files (without sensitive data).
Drag and drop here or browse files to attachMaximum size: 71 MB • Maximum attachments allowed: 5Thank you for your understanding and patience
I know I don't know anything (Socrates)
Hi NikolinoDE, many thanks.
OS: Windows 10
Excel version: H&S 2019
Fil'e attached. It's public data, no sensitive info.
Tanks
Luis
Crear o eliminar una lista personalizada para ordenar y rellenar datos
In the attached file the example with the table.
Hope I was able to help you with this information.
I know I don't know anything (Socrates)
