Mar 05 2022 04:15 AM
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
Mar 05 2022 04:24 AM - edited Mar 05 2022 04:26 AM
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).
Thank you for your understanding and patience
I know I don't know anything (Socrates)
Mar 05 2022 06:27 AM
Hi @NikolinoDE, many thanks.
OS: Windows 10
Excel version: H&S 2019
Fil'e attached. It's public data, no sensitive info.
Tanks
Luis
Mar 05 2022 06:43 AM
Mar 05 2022 06:51 AM
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)
Mar 05 2022 09:11 AM
Mar 05 2022 10:23 PM
Solution@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.
Mar 06 2022 04:36 AM
Mar 06 2022 05:33 AM
Mar 05 2022 10:23 PM
Solution@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.