Average based on time

baqer

New Member
Joined
May 12, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
i have a excel file many column data like this
1400-2-4.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Date/Time;rainfall-2m;temprature-10m;windspeed-10m;winddirection-10m;tempratore-45m;windspeed-45m;windirection-45m;temprature-80m;windspeed-80m;winddirection-80m;temperature-100m;windspeed-100m;winddirection-100m;humidity-10m;humidity-45m;humidity-80m;humidity-100m
224/04/202112:01:00 AM;0.0;27.3;1.3;98.0;29.7;2.6;58.0;31.3;3.8;254.0;32.2;4.1;28.0;30.8;31.3;18.1;12.2;
324/04/202112:02:01 AM;0.0;27.3;1.1;93.0;29.7;2.4;53.0;31.3;4.3;251.0;32.4;4.4;22.0;30.8;33.6;18.7;11.2;
424/04/202112:03:02 AM;0.0;27.3;1.0;93.0;29.7;2.7;53.0;31.5;4.2;254.0;32.7;4.2;34.0;30.8;32.7;16.4;11.0;
524/04/202112:04:03 AM;0.0;27.3;1.1;93.0;29.8;2.8;58.0;31.6;4.3;258.0;32.8;4.2;39.0;30.8;32.9;18.5;12.3;
624/04/202112:05:04 AM;0.0;27.2;0.9;102.0;29.7;2.6;55.0;31.5;4.0;258.0;32.7;4.1;40.0;30.7;32.9;18.1;12.5;
724/04/202112:06:05 AM;0.0;27.2;1.0;101.0;29.7;3.4;51.0;31.4;4.6;266.0;32.6;3.9;50.0;30.7;32.9;20.4;13.0;
824/04/202112:07:06 AM;0.0;0.0;1.1;97.0;29.8;3.7;48.0;31.3;5.0;268.0;32.5;4.4;51.0;0.0;31.4;18.1;13.8;
924/04/202112:08:07 AM;0.0;27.2;1.2;83.0;29.9;3.6;46.0;31.5;5.0;268.0;32.3;4.4;58.0;30.7;28.3;17.0;13.8;
1024/04/202112:09:08 AM;0.0;27.2;1.3;81.0;30.1;3.7;44.0;31.6;4.8;269.0;32.2;4.3;60.0;30.7;25.9;16.0;13.8;
1124/04/202112:10:10 AM;0.0;27.2;1.3;121.0;30.4;3.4;51.0;31.7;4.4;268.0;32.1;4.2;57.0;30.7;26.5;16.6;14.2;
1224/04/202112:11:11 AM;0.0;27.2;1.3;139.0;30.5;3.6;63.0;31.7;4.2;268.0;32.0;4.0;56.0;30.7;29.5;18.2;14.3;
1324/04/202112:12:12 AM;0.0;27.2;1.6;92.0;30.4;3.7;43.0;31.7;4.6;268.0;32.0;3.9;45.0;30.7;29.2;17.2;13.6;
1424/04/202112:13:13 AM;0.0;27.2;1.7;94.0;30.5;3.4;49.0;31.8;4.5;264.0;32.1;4.2;40.0;30.7;25.8;15.8;11.5;
1524/04/202112:15:07 AM;0.0;27.3;1.9;88.0;30.8;3.6;44.0;32.0;4.6;271.0;32.5;4.0;53.0;31.0;24.3;15.8;12.2;
1624/04/202112:16:08 AM;0.0;27.3;1.8;89.0;30.9;3.6;40.0;32.1;4.5;271.0;32.6;3.9;54.0;31.0;22.7;15.5;12.7;
1724/04/202112:17:09 AM;0.0;27.4;1.7;89.0;31.1;3.8;39.0;32.2;4.4;271.0;32.6;3.9;64.0;31.1;21.5;13.9;13.3;
1824/04/202112:18:10 AM;0.0;27.5;1.6;88.0;31.2;3.6;35.0;32.3;4.5;268.0;32.5;3.7;42.0;31.1;21.7;13.6;13.3;
1924/04/202112:19:11 AM;0.0;27.5;1.4;89.0;31.3;3.6;35.0;32.3;4.3;263.0;32.5;3.9;32.0;31.1;21.9;14.1;11.9;
2024/04/202112:20:12 AM;0.0;27.5;1.4;84.0;31.4;3.5;33.0;32.4;4.0;258.0;32.7;4.1;26.0;31.1;21.4;14.2;10.9;
2124/04/202112:21:13 AM;0.0;27.5;1.4;78.0;31.4;3.6;32.0;32.5;4.2;254.0;32.8;4.0;33.0;31.1;21.1;13.6;10.4;
2224/04/202112:22:14 AM;0.0;27.5;1.5;78.0;31.5;3.5;31.0;32.6;4.4;260.0;33.0;3.9;34.0;31.2;21.1;13.9;10.8;
2324/04/202112:23:15 AM;0.0;27.5;1.3;72.0;31.6;3.5;31.0;32.6;4.3;257.0;33.0;3.9;33.0;31.2;21.2;13.8;10.8;
2424/04/202112:24:16 AM;0.0;27.5;1.1;75.0;31.6;3.3;31.0;32.6;4.1;262.0;33.0;3.9;35.0;31.2;21.2;13.6;10.8;
2524/04/202112:25:17 AM;0.0;27.4;1.2;80.0;31.5;3.1;35.0;32.6;4.1;264.0;33.0;3.8;48.0;31.3;23.0;12.5;11.5;
2624/04/202112:26:18 AM;0.0;27.3;1.1;80.0;31.4;3.1;31.0;32.5;4.1;264.0;33.0;3.6;48.0;31.4;23.3;12.6;12.1;
2724/04/202112:27:19 AM;0.0;27.3;0.9;81.0;31.3;3.0;31.0;32.5;4.0;264.0;32.7;3.6;49.0;31.5;23.2;12.8;12.6;
2824/04/202112:28:20 AM;0.0;27.2;1.0;77.0;31.2;3.0;28.0;32.5;4.0;264.0;32.6;3.6;49.0;31.6;23.2;12.7;12.8;
2924/04/202112:29:22 AM;0.0;27.1;1.0;78.0;31.1;2.9;29.0;32.5;3.8;265.0;32.6;3.6;46.0;31.7;23.5;12.6;12.8;
3024/04/202112:30:23 AM;0.0;0.0;1.1;77.0;31.0;3.0;31.0;32.5;3.8;264.0;32.5;3.6;46.0;0.0;23.8;12.6;12.5;
3124/04/202112:31:24 AM;0.0;27.0;1.0;80.0;31.0;3.0;31.0;32.5;3.8;264.0;32.6;3.6;44.0;31.9;24.0;12.6;12.5;
3224/04/202112:32:25 AM;0.0;27.0;1.0;78.0;31.0;3.1;32.0;32.5;3.8;264.0;32.6;3.7;43.0;31.9;23.1;12.6;12.0;
3324/04/202112:33:26 AM;0.0;27.0;1.1;78.0;31.0;3.1;35.0;32.5;3.8;271.0;32.6;3.6;46.0;31.9;22.5;12.6;12.0;
3424/04/202112:34:27 AM;0.0;27.0;1.2;90.0;31.1;3.0;36.0;32.5;3.3;276.0;32.6;3.5;55.0;31.9;23.2;13.1;12.2;
3524/04/202112:35:28 AM;0.0;26.9;1.2;97.0;31.0;2.9;41.0;32.5;3.5;275.0;32.6;3.6;59.0;32.0;23.9;13.3;11.9;
3624/04/202112:36:29 AM;0.0;26.9;1.4;99.0;31.0;3.0;40.0;32.5;3.4;275.0;32.5;3.5;60.0;32.0;24.4;13.4;11.9;
3724/04/202112:37:30 AM;0.0;26.8;1.4;99.0;30.9;3.0;40.0;32.4;3.1;276.0;32.5;3.7;62.0;31.9;24.7;13.5;12.1;
3824/04/202112:38:31 AM;0.0;26.7;1.3;103.0;30.9;3.0;44.0;32.4;3.2;276.0;32.5;3.6;60.0;31.9;25.0;13.4;12.1;
3924/04/202112:39:32 AM;0.0;26.7;1.3;94.0;30.8;3.1;40.0;32.3;3.1;278.0;32.4;3.6;73.0;31.8;25.4;13.5;12.3;
4024/04/202112:40:33 AM;0.0;26.8;1.3;93.0;30.8;3.1;41.0;32.3;2.6;280.0;32.4;3.7;68.0;31.9;24.4;13.5;12.4;
4124/04/202112:41:34 AM;0.0;26.8;1.0;93.0;30.8;3.2;40.0;32.3;2.8;278.0;32.3;3.3;66.0;31.9;23.9;13.8;12.4;
4224/04/202112:42:35 AM;0.0;26.8;0.9;93.0;30.9;2.9;39.0;32.2;2.9;276.0;32.3;3.3;61.0;31.9;23.5;14.8;12.2;
4324/04/202112:43:36 AM;0.0;26.8;1.0;93.0;30.8;2.9;40.0;32.2;3.0;275.0;32.3;3.2;61.0;31.9;25.4;14.6;12.2;
4424/04/202112:44:37 AM;0.0;26.8;0.9;94.0;30.8;2.7;44.0;32.2;2.1;277.0;32.3;3.3;72.0;31.8;25.3;14.8;12.6;
4524/04/202112:45:39 AM;0.0;26.8;0.8;100.0;30.8;2.7;55.0;32.1;3.5;275.0;32.3;3.3;79.0;31.7;27.4;14.4;14.0;
4624/04/202112:46:40 AM;0.0;26.8;0.9;102.0;30.6;2.9;55.0;32.0;3.3;276.0;32.2;3.5;90.0;31.9;28.5;15.1;14.3;
4724/04/202112:47:41 AM;0.0;26.7;1.0;106.0;30.5;2.8;52.0;32.0;2.0;285.0;32.2;3.7;92.0;31.8;28.8;15.1;14.6;
4824/04/202112:48:42 AM;0.0;26.7;0.9;106.0;30.5;2.8;51.0;31.9;2.2;280.0;32.1;3.5;99.0;31.8;27.9;15.4;14.6;
4924/04/202112:49:43 AM;0.0;26.7;0.8;110.0;30.5;2.9;49.0;31.9;2.6;285.0;32.1;3.5;96.0;31.8;26.5;15.7;15.6;
5024/04/202112:50:44 AM;0.0;26.7;0.7;110.0;30.5;2.9;47.0;31.9;2.7;288.0;32.1;3.5;98.0;31.8;25.1;16.2;15.7;
5124/04/202112:51:45 AM;0.0;0.0;0.0;110.0;30.6;3.0;47.0;31.9;2.5;290.0;32.1;3.3;99.0;0.0;24.4;17.3;16.8;
5224/04/202112:52:46 AM;0.0;26.7;0.0;110.0;30.7;3.0;43.0;31.9;2.6;299.0;32.0;3.0;91.0;31.8;24.2;17.7;18.2;
5324/04/202112:53:47 AM;0.0;26.7;0.0;110.0;30.8;3.1;44.0;31.8;2.5;293.0;31.9;3.2;99.0;31.8;23.0;18.1;16.3;
5424/04/202112:54:48 AM;0.0;26.7;0.9;101.0;30.9;3.1;44.0;31.8;2.4;287.0;31.9;3.0;79.0;31.8;23.6;18.4;16.5;
5524/04/202112:55:49 AM;0.0;26.8;0.8;105.0;30.9;3.1;43.0;31.8;2.5;285.0;31.9;3.0;96.0;31.7;23.6;18.8;16.8;
5624/04/202112:56:50 AM;0.0;26.8;0.8;101.0;30.9;3.3;44.0;31.8;2.5;290.0;32.0;3.1;98.0;31.7;23.7;19.1;17.2;
5724/04/202112:57:51 AM;0.0;26.8;0.9;104.0;31.0;3.2;46.0;31.7;2.6;294.0;32.0;3.1;93.0;31.6;21.7;19.2;17.1;
5824/04/202112:58:52 AM;0.0;26.8;1.0;103.0;31.1;3.2;48.0;31.7;2.2;285.0;31.9;3.1;83.0;31.5;21.0;19.0;16.2;
5924/04/202112:59:53 AM;0.0;26.9;0.9;102.0;31.2;2.9;51.0;31.7;1.7;284.0;31.9;3.0;77.0;31.7;19.9;18.9;15.7;
6024/04/20211:00:54 AM;0.0;26.9;1.0;102.0;31.3;2.8;49.0;31.8;1.4;278.0;32.0;3.0;64.0;31.4;18.8;18.4;15.3;
6124/04/20211:01:56 AM;0.0;26.9;0.9;101.0;31.4;2.8;53.0;31.8;1.6;285.0;32.0;3.0;65.0;31.2;18.3;17.9;14.6;
6224/04/20211:02:57 AM;0.0;26.9;1.1;102.0;31.5;2.9;51.0;31.9;1.7;285.0;32.1;2.9;77.0;31.1;18.4;17.6;14.4;
6324/04/20211:03:58 AM;0.0;27.0;1.2;95.0;31.6;3.1;50.0;31.9;1.9;279.0;32.2;2.8;76.0;31.0;18.4;17.4;14.8;
6424/04/20211:04:59 AM;0.0;27.0;1.2;97.0;31.6;2.9;47.0;32.0;1.6;282.0;32.2;2.8;59.0;30.7;18.5;17.3;14.6;
6524/04/20211:06:00 AM;0.0;27.0;1.0;96.0;31.6;2.7;46.0;32.0;1.3;283.0;32.2;2.6;60.0;30.7;18.6;17.3;13.9;
6624/04/20211:07:01 AM;0.0;27.1;0.9;97.0;31.6;2.6;48.0;32.0;1.6;284.0;32.2;2.9;55.0;30.8;18.3;17.2;13.5;
6724/04/20211:08:02 AM;0.0;27.1;1.0;101.0;31.7;2.7;44.0;32.1;1.9;279.0;32.3;2.6;64.0;30.8;18.1;17.1;13.6;
6824/04/20211:09:03 AM;0.0;27.1;1.1;99.0;31.7;2.6;50.0;32.1;2.5;275.0;32.4;2.8;57.0;30.8;18.2;17.0;14.1;
6924/04/20211:10:04 AM;0.0;27.1;1.1;96.0;31.7;2.6;48.0;32.1;2.9;271.0;32.4;3.1;51.0;30.7;19.9;16.6;13.4;
7024/04/20211:11:05 AM;0.0;27.1;1.2;97.0;31.7;2.5;50.0;32.2;3.0;268.0;32.5;3.3;43.0;30.7;20.0;16.3;12.9;
7124/04/20211:12:06 AM;0.0;27.1;1.3;74.0;31.7;2.5;49.0;32.2;3.0;265.0;32.5;3.5;38.0;30.7;22.1;16.1;12.7;
7224/04/20211:13:07 AM;0.0;27.2;1.2;74.0;31.7;2.4;53.0;32.3;3.2;255.0;32.6;4.0;34.0;30.8;22.1;15.1;12.0;
7324/04/20211:14:08 AM;0.0;27.2;1.3;71.0;31.8;2.4;49.0;32.4;3.4;254.0;32.8;4.2;32.0;30.9;20.4;14.2;11.4;
7424/04/20211:15:09 AM;0.0;27.3;1.3;73.0;31.9;2.5;50.0;32.5;3.3;257.0;32.9;4.0;33.0;30.8;19.8;14.0;11.2;
7524/04/20211:16:10 AM;0.0;27.3;1.3;73.0;32.0;2.5;49.0;32.6;3.2;261.0;33.0;3.8;34.0;30.8;18.3;13.8;11.2;
7624/04/20211:17:11 AM;0.0;27.3;1.3;72.0;32.0;2.5;52.0;32.7;3.3;259.0;33.0;3.5;34.0;30.8;18.5;13.7;11.2;
7724/04/20211:18:13 AM;0.0;27.3;1.2;72.0;32.1;2.4;53.0;32.7;3.2;257.0;33.1;3.6;34.0;30.8;17.2;13.7;11.5;
7824/04/20211:19:14 AM;0.0;27.3;1.3;72.0;32.2;2.4;50.0;32.7;3.1;254.0;33.1;3.6;27.0;30.9;17.0;13.8;11.4;
7924/04/20211:20:15 AM;0.0;27.3;1.3;73.0;32.2;2.4;50.0;32.7;3.0;259.0;33.1;3.5;34.0;30.8;16.8;14.0;11.4;
8024/04/20211:21:16 AM;0.0;27.3;1.1;74.0;32.2;2.3;48.0;32.7;2.8;258.0;33.2;3.4;35.0;30.8;17.6;14.3;11.5;
8124/04/20211:22:17 AM;0.0;27.2;1.2;72.0;32.2;2.4;51.0;32.7;2.8;263.0;33.1;3.3;37.0;30.7;18.3;14.6;12.0;
8224/04/20211:23:18 AM;0.0;27.2;1.2;75.0;32.2;2.6;49.0;32.6;2.9;265.0;33.1;3.0;48.0;30.9;19.0;15.2;12.2;
8324/04/20211:24:19 AM;0.0;27.2;1.2;75.0;32.1;2.5;52.0;32.6;2.9;265.0;33.0;3.4;43.0;30.7;18.9;15.4;12.1;
8424/04/20211:25:20 AM;0.0;27.2;1.0;75.0;32.1;2.6;51.0;32.6;2.9;265.0;33.0;3.2;43.0;30.7;19.3;15.4;12.3;
8524/04/20211:26:21 AM;0.0;27.2;1.0;93.0;32.1;2.4;52.0;32.6;2.7;271.0;32.9;3.1;47.0;30.7;20.2;15.4;12.5;
8624/04/20211:27:22 AM;0.0;27.2;1.1;94.0;32.0;2.7;52.0;32.5;2.8;275.0;32.9;2.9;45.0;30.7;20.8;15.8;12.9;
8724/04/20211:28:23 AM;0.0;27.2;1.2;93.0;31.9;2.8;50.0;32.5;3.2;271.0;32.8;3.0;50.0;30.7;21.0;15.7;13.0;
8824/04/20211:29:24 AM;0.0;27.3;1.6;94.0;31.9;2.8;51.0;32.5;3.3;268.0;32.7;3.3;48.0;30.8;21.6;15.3;13.0;
8924/04/20211:30:25 AM;0.0;27.3;1.7;89.0;31.9;2.8;54.0;32.5;3.2;265.0;32.7;3.3;42.0;30.9;21.1;14.8;12.9;
9024/04/20211:31:26 AM;0.0;27.5;1.7;87.0;31.9;2.9;55.0;32.5;3.3;265.0;32.7;3.3;41.0;30.7;20.0;14.5;13.0;
9124/04/20211:32:27 AM;0.0;27.5;1.5;84.0;31.9;3.0;55.0;32.5;3.4;264.0;32.7;3.4;41.0;30.5;19.1;14.3;12.8;
9224/04/20211:33:29 AM;0.0;27.6;1.6;92.0;32.0;3.2;55.0;32.6;3.3;262.0;32.8;3.5;44.0;30.5;17.5;14.2;12.5;
9324/04/20211:34:30 AM;0.0;27.6;1.8;97.0;32.1;3.2;57.0;32.6;3.4;268.0;32.8;3.5;45.0;30.4;17.9;14.3;12.6;
9424/04/20211:35:31 AM;0.0;27.7;1.8;93.0;32.1;3.3;57.0;32.6;3.3;272.0;32.7;3.5;49.0;30.3;17.6;14.3;12.6;
9524/04/20211:36:32 AM;0.0;27.7;1.8;88.0;32.1;3.2;56.0;32.6;3.3;270.0;32.7;3.5;47.0;29.9;17.1;14.3;12.6;
9624/04/20211:37:33 AM;0.0;27.7;1.7;91.0;32.1;3.0;59.0;32.5;3.5;268.0;32.7;3.7;49.0;29.8;16.7;13.9;12.7;
9724/04/20211:38:34 AM;0.0;27.7;1.8;88.0;32.2;3.0;56.0;32.6;3.6;271.0;32.7;3.6;50.0;29.9;16.7;13.8;12.8;
9824/04/20211:39:35 AM;0.0;27.7;1.9;91.0;32.2;3.3;56.0;32.6;3.5;271.0;32.8;3.6;52.0;30.0;16.4;13.8;12.7;
9924/04/20211:40:36 AM;0.0;27.7;1.8;87.0;32.2;3.2;42.0;32.6;3.8;271.0;32.7;3.6;54.0;30.1;16.4;13.8;12.7;
10024/04/20211:41:37 AM;0.0;27.6;1.5;82.0;32.2;3.2;56.0;32.6;3.7;271.0;32.7;3.7;50.0;30.1;16.2;13.7;12.8;
10124/04/20211:42:38 AM;0.0;27.6;1.5;92.0;32.3;3.3;55.0;32.6;3.3;271.0;32.7;3.7;47.0;30.1;16.0;14.0;12.9;
10224/04/20211:43:39 AM;0.0;27.6;1.6;87.0;32.3;3.4;56.0;32.6;2.6;278.0;32.7;3.5;51.0;30.3;17.3;14.4;12.8;
10324/04/20211:44:40 AM;0.0;27.5;1.6;92.0;32.2;3.3;54.0;32.5;2.8;276.0;32.7;3.3;60.0;30.7;18.8;15.2;12.9;
10424/04/20211:45:41 AM;0.0;27.5;1.6;93.0;32.2;3.4;52.0;32.5;2.7;278.0;32.7;3.4;57.0;31.0;18.6;15.2;13.0;
10524/04/20211:46:42 AM;0.0;27.5;1.8;81.0;32.1;3.4;52.0;32.4;2.8;275.0;32.6;3.4;50.0;31.0;19.1;15.5;13.0;
10624/04/20211:47:43 AM;0.0;27.5;1.3;84.0;32.1;3.2;54.0;32.4;3.2;271.0;32.5;3.6;41.0;31.0;18.1;15.3;12.9;
10724/04/20211:48:45 AM;0.0;27.5;1.3;81.0;32.0;3.0;53.0;32.4;3.2;266.0;32.6;3.6;41.0;31.0;17.1;14.9;12.9;
10824/04/20211:49:46 AM;0.0;27.4;1.2;84.0;32.1;3.1;54.0;32.4;3.2;267.0;32.7;3.5;45.0;30.9;16.4;14.7;12.9;
10924/04/20211:50:47 AM;0.0;27.4;1.3;88.0;32.1;3.1;57.0;32.4;3.3;271.0;32.6;3.5;49.0;30.9;16.8;14.6;12.9;
11024/04/20211:51:48 AM;0.0;27.4;1.2;93.0;32.1;3.2;56.0;32.5;2.5;273.0;32.6;3.4;51.0;30.9;17.7;14.6;13.0;
11124/04/20211:52:49 AM;0.0;27.4;1.3;101.0;32.1;3.3;57.0;32.5;2.1;279.0;32.7;3.2;56.0;30.9;18.7;15.1;13.0;
11224/04/20211:53:50 AM;0.0;27.4;1.7;98.0;32.0;3.5;55.0;32.4;2.3;277.0;32.6;3.4;60.0;30.9;19.3;15.6;13.0;
11324/04/20211:54:51 AM;0.0;27.3;1.8;102.0;32.0;3.4;55.0;32.4;2.2;280.0;32.6;3.5;63.0;30.8;21.2;15.7;13.2;
11424/04/20211:55:52 AM;0.0;27.2;1.8;98.0;31.9;3.5;53.0;32.3;2.1;281.0;32.5;3.6;64.0;30.7;21.0;15.7;13.4;
11524/04/20211:56:53 AM;0.0;27.2;1.9;103.0;31.8;3.6;57.0;32.3;2.4;281.0;32.5;3.5;59.0;30.7;20.3;15.6;13.5;
11624/04/20211:57:54 AM;0.0;27.2;1.8;106.0;31.8;3.4;56.0;32.3;2.2;282.0;32.5;3.5;59.0;30.7;20.8;15.3;13.3;
11724/04/20211:58:55 AM;0.0;27.2;1.5;115.0;31.8;3.1;67.0;32.3;2.3;278.0;32.5;3.3;58.0;30.7;21.1;15.3;13.5;
11824/04/20211:59:56 AM;0.0;27.2;1.5;115.0;31.6;3.6;56.0;32.3;1.8;279.0;32.5;3.2;64.0;30.7;21.2;15.3;13.5;
11924/04/20212:00:58 AM;0.0;27.1;2.1;112.0;31.6;3.7;58.0;32.3;1.6;275.0;32.5;3.4;68.0;30.7;21.6;15.2;13.6;
Sheet1



can any help my to create vba macro to average of range that Contains all temp and all humidity and also all range that Contains win speed and win direction for every one hour and every 10 Minutes to other sheet .
may day is one day my time is 24 hour 12:00:00AM to 23:59:59PM
thank for help
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
385
Office Version
  1. 365
Platform
  1. Windows
Very rough code based on what I could copy from your table above. Note that I moved the Time column header to the right. Your data splitting based on didn't separate the header cells the same as the data. Since I don't have your real data, this is just a starting point. Also, it is not optimized: the code uses 17 columns of data, but there might be more or less in the real table, for example. Other anomalies might present themselves if you run this on your code simply because your data is not in the same locations as mine (due to my guessing of what your real data looks like).

I used arrays to hold the 10-minute intervals starting with the end of the first interval (12:10 AM). If the time in column 2 is less than the interval time, then the data is averaged. If not, the next interval is started.

Anyway, here is a start:

VBA Code:
Sub Averages()
    Dim d As Date
    Dim tenMins As Double, oneHour As Double
    Dim AveragesOfTenMinIntervals(143, 17) As Double
    Dim AveragesOfOneHrIntervals(23, 17) As Double
    Dim i As Integer, colCount As Integer
    Dim activeRow As Integer
    Dim avg As Double, avgCount As Integer
    
    activeRow = 2
    d = CDate(Cells(2, 1).Value)
    oneHour = 1 / 24
    tenMins = oneHour / 6
    
    'Run through data to fill averages of every 10 min interval in 24-hour period
    For i = 0 To 143
        avg = 0
        avgCount = 0
        Do While Cells(activeRow, 2).Value <> ""
            If CDate(Cells(activeRow, 1)) = d Then
                If CDate(Replace(Cells(activeRow, 2).Value, ";", "")) < (i + 1) * tenMins Then
                    For colCount = 0 To 16
                        AveragesOfTenMinIntervals(i, colCount) = AveragesOfTenMinIntervals(i, colCount) + CDbl(Replace(Cells(activeRow, 3 + colCount).Value, ";", ""))
                    Next
                    avgCount = avgCount + 1
                    activeRow = activeRow + 1
                Else
                    Exit Do
                End If
            End If
        Loop
        If avgCount > 0 Then
            For colCount = 0 To 16
                AveragesOfTenMinIntervals(i, colCount) = AveragesOfTenMinIntervals(i, colCount) / avgCount
            Next
        End If
    Next
    
    'Run through data to fill averages of every 1 hour interval in 24-hour period
    activeRow = 2
    For i = 0 To 23
        avg = 0
        avgCount = 0
        Do While Cells(activeRow, 2).Value <> ""
            If CDate(Cells(activeRow, 1)) = d Then
                If CDate(Replace(Cells(activeRow, 2).Value, ";", "")) < (i + 1) * oneHour Then
                    For colCount = 0 To 16
                        AveragesOfOneHrIntervals(i, colCount) = AveragesOfOneHrIntervals(i, colCount) + CDbl(Replace(Cells(activeRow, 3 + colCount).Value, ";", ""))
                    Next
                    avgCount = avgCount + 1
                    activeRow = activeRow + 1
                Else
                    Exit Do
                End If
            End If
        Loop
        If avgCount > 0 Then
            For colCount = 0 To 16
                AveragesOfOneHrIntervals(i, colCount) = AveragesOfOneHrIntervals(i, colCount) / avgCount
            Next
        End If
    Next
    
    'Create reporting sheets
    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Set sh1 = ActiveSheet
    Set sh2 = Worksheets.Add(After:=sh1)
    Set sh3 = Worksheets.Add(After:=sh2)
    sh2.Name = "10 Min Averages"
    sh3.Name = "1 Hour Averages"
    sh1.Activate
    Rows("1:1").Copy sh2.Cells(1, 1)
    Rows("1:1").Copy sh3.Cells(1, 1)
    
    'Fill 10 Min Averages sheet
    sh2.Activate
    Columns("A:A").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("B:B").Select
    Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Range("A1").Select
    activeRow = 2
    For i = 0 To 143
        Cells(activeRow, 1).Value = d
        Cells(activeRow, 2).Value = (i + 1) * tenMins
        For colCount = 0 To 16
            Cells(activeRow, 3 + colCount).Value = AveragesOfTenMinIntervals(i, colCount)
        Next
        activeRow = activeRow + 1
    Next
    
    'Fill 1 Hr Averages sheet
    sh3.Activate
    Columns("A:A").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("B:B").Select
    Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Range("A1").Select
    activeRow = 2
    For i = 0 To 23
        Cells(activeRow, 1).Value = d
        Cells(activeRow, 2).Value = (i + 1) * oneHour
        For colCount = 0 To 16
            Cells(activeRow, 3 + colCount).Value = AveragesOfOneHrIntervals(i, colCount)
        Next
        activeRow = activeRow + 1
    Next
End Sub
 

baqer

New Member
Joined
May 12, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Excellent and thank you very very mach for help shknbk2
i am very basic for vba
the code that you write for my solution have in line type mismatch error 13
i am upload file to google drive and share whit you my excel file for you
please help for solved error runtime 13 and i have other Question
the win speed and win directions need other formula to get the average like look =MOD(DEGREES(ATAN2(SUM(COS(RADIANS(A2:C2)) * ISNUMBER(A2:C2)), SUM(SIN(RADIANS(A2:C2))))), 360) can you help shknbk2 ?????
thanks
 

baqer

New Member
Joined
May 12, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

thank you very mach @@shknbk2
the problem as runtime error 13 is solved @ many thanks
problem is Because my data was irregular and there was empty space between the numbers
But the main problem is still that a separate formula should be used for the average wind direction and wind speed, which I ask you to help if possible, because I have to do this every day. Thank you very much.
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
385
Office Version
  1. 365
Platform
  1. Windows
Can you say in words what the speed and direction averages need to calculate? Since A2:C2 in your data don't relate to any speed or direction column, I'm not following what you are trying to do.

Also, is whatever formula you want to be calculated then supposed to calculate the value for the timed average? For example, calculate the average over E2:E10 since that is the first 10 minute interval for the windspeed-10m? Also do the same for each hour calculation?
 

baqer

New Member
Joined
May 12, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

hello @shknbk2

If the columns containing wind speed and wind direction can be calculated as an average of 10 minutes and 1 hour, I do not know their calculation formula, but my request is that it be calculated like temp and humidity
 

baqer

New Member
Joined
May 12, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
This formula not for my file I am copy this formula from internet for example
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
385
Office Version
  1. 365
Platform
  1. Windows
I still don't know what kind of formula you are looking for. However, to make it easier for you to put in the formula you want for the appropriate columns, I changed the code to be formula-based. When the 2 new sheets are created, they are filled with AVERAGE formulas that reference the relevant rows that make up the 10-min or hour time periods. In each section, the AVERAGE formula is used to fill in the arrays. It should be simpler this way to change the formulas in the columns you want to have something other than AVERAGE.

Also, in order to use the AVERAGE formulas, I first remove all semicolons in the data.

VBA Code:
Sub Average_Formulas()
    Dim d As Date
    Dim tenMins As Double, oneHour As Double
    Dim AveragesOfTenMinIntervals(143, 16) As String
    Dim AveragesOfOneHrIntervals(23, 16) As String
    Dim i As Integer, colCount As Integer
    Dim activeRow As Integer
    Dim avg As Double, avgCount As Integer
    Dim avgFirst As Long, avgLast As Long
    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    
    Set sh1 = ActiveSheet
    activeRow = 2
    d = CDate(Cells(2, 1).Value)
    oneHour = 1 / 24
    tenMins = oneHour / 6
    
    Cells.Replace What:=";", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
        FormulaVersion:=xlReplaceFormula2

    'Run through data to fill averages of every 10 min interval in 24-hour period
    For i = 0 To 143
        avgFirst = 0
        avgLast = 0
        Do While Cells(activeRow, 2).Value <> ""
            If CDate(Cells(activeRow, 1)) = d Then
                If CDate(Cells(activeRow, 2).Value) < (i + 1) * tenMins Then
                    If avgFirst = 0 Then
                        avgFirst = activeRow
                    End If
                Else
                    If avgFirst < activeRow Then
                        avgLast = activeRow - 1
                    Else
                        avgLast = activeRow
                    End If
                    Exit Do
                End If
                activeRow = activeRow + 1
            End If
        Loop
        If avgFirst > 0 Then
            If avgLast = 0 Then
                If avgFirst < activeRow Then
                    avgLast = activeRow - 1
                Else
                    avgLast = activeRow
                End If
            End If
            AveragesOfTenMinIntervals(i, 0) = "=AVERAGE(" & sh1.Name & "!C" & avgFirst & ":C" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 1) = "=AVERAGE(" & sh1.Name & "!D" & avgFirst & ":D" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 0) = "=AVERAGE(" & sh1.Name & "!E" & avgFirst & ":E" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 2) = "=AVERAGE(" & sh1.Name & "!F" & avgFirst & ":F" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 3) = "=AVERAGE(" & sh1.Name & "!G" & avgFirst & ":G" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 4) = "=AVERAGE(" & sh1.Name & "!H" & avgFirst & ":H" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 5) = "=AVERAGE(" & sh1.Name & "!I" & avgFirst & ":I" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 6) = "=AVERAGE(" & sh1.Name & "!J" & avgFirst & ":J" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 7) = "=AVERAGE(" & sh1.Name & "!K" & avgFirst & ":K" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 8) = "=AVERAGE(" & sh1.Name & "!L" & avgFirst & ":L" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 9) = "=AVERAGE(" & sh1.Name & "!M" & avgFirst & ":M" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 10) = "=AVERAGE(" & sh1.Name & "!N" & avgFirst & ":N" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 11) = "=AVERAGE(" & sh1.Name & "!O" & avgFirst & ":O" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 12) = "=AVERAGE(" & sh1.Name & "!P" & avgFirst & ":P" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 13) = "=AVERAGE(" & sh1.Name & "!Q" & avgFirst & ":Q" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 14) = "=AVERAGE(" & sh1.Name & "!R" & avgFirst & ":R" & avgLast & ")"
            AveragesOfTenMinIntervals(i, 15) = "=AVERAGE(" & sh1.Name & "!S" & avgFirst & ":S" & avgLast & ")"
        End If
    Next
    
    'Run through data to fill averages of every 1 hour interval in 24-hour period
    activeRow = 2
    For i = 0 To 23
        avgFirst = 0
        avgLast = 0
        Do While Cells(activeRow, 2).Value <> ""
            If CDate(Cells(activeRow, 1)) = d Then
                If CDate(Cells(activeRow, 2).Value) < (i + 1) * oneHour Then
                    If avgFirst = 0 Then
                        avgFirst = activeRow
                    End If
                Else
                    If avgFirst < activeRow Then
                        avgLast = activeRow - 1
                    Else
                        avgLast = activeRow
                    End If
                    Exit Do
                End If
                activeRow = activeRow + 1
            End If
        Loop
        If avgFirst > 0 Then
            If avgLast = 0 Then
                If avgFirst < activeRow Then
                    avgLast = activeRow - 1
                Else
                    avgLast = activeRow
                End If
            End If
            AveragesOfOneHrIntervals(i, 0) = "=AVERAGE(" & sh1.Name & "!C" & avgFirst & ":C" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 1) = "=AVERAGE(" & sh1.Name & "!D" & avgFirst & ":D" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 0) = "=AVERAGE(" & sh1.Name & "!E" & avgFirst & ":E" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 2) = "=AVERAGE(" & sh1.Name & "!F" & avgFirst & ":F" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 3) = "=AVERAGE(" & sh1.Name & "!G" & avgFirst & ":G" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 4) = "=AVERAGE(" & sh1.Name & "!H" & avgFirst & ":H" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 5) = "=AVERAGE(" & sh1.Name & "!I" & avgFirst & ":I" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 6) = "=AVERAGE(" & sh1.Name & "!J" & avgFirst & ":J" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 7) = "=AVERAGE(" & sh1.Name & "!K" & avgFirst & ":K" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 8) = "=AVERAGE(" & sh1.Name & "!L" & avgFirst & ":L" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 9) = "=AVERAGE(" & sh1.Name & "!M" & avgFirst & ":M" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 10) = "=AVERAGE(" & sh1.Name & "!N" & avgFirst & ":N" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 11) = "=AVERAGE(" & sh1.Name & "!O" & avgFirst & ":O" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 12) = "=AVERAGE(" & sh1.Name & "!P" & avgFirst & ":P" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 13) = "=AVERAGE(" & sh1.Name & "!Q" & avgFirst & ":Q" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 14) = "=AVERAGE(" & sh1.Name & "!R" & avgFirst & ":R" & avgLast & ")"
            AveragesOfOneHrIntervals(i, 15) = "=AVERAGE(" & sh1.Name & "!S" & avgFirst & ":S" & avgLast & ")"
        End If
    Next
    
    'Create reporting sheets
    Set sh2 = Worksheets.Add(After:=sh1)
    Set sh3 = Worksheets.Add(After:=sh2)
    sh2.Name = "10 Min Averages"
    sh3.Name = "1 Hour Averages"
    sh1.Activate
    Rows("1:1").Copy sh2.Cells(1, 1)
    Rows("1:1").Copy sh3.Cells(1, 1)
    
    'Fill 10 Min Averages sheet
    sh2.Activate
    Columns("A:A").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("B:B").Select
    Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Range("A1").Select
    activeRow = 2
    For i = 0 To 143
        Cells(activeRow, 1).Value = d
        Cells(activeRow, 2).Value = (i + 1) * tenMins
        For colCount = 0 To 16
            Cells(activeRow, 3 + colCount).Value = AveragesOfTenMinIntervals(i, colCount)
        Next
        activeRow = activeRow + 1
    Next
    
    'Fill 1 Hr Averages sheet
    sh3.Activate
    Columns("A:A").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("B:B").Select
    Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Range("A1").Select
    activeRow = 2
    For i = 0 To 23
        Cells(activeRow, 1).Value = d
        Cells(activeRow, 2).Value = (i + 1) * oneHour
        For colCount = 0 To 16
            Cells(activeRow, 3 + colCount).Value = AveragesOfOneHrIntervals(i, colCount)
        Next
        activeRow = activeRow + 1
    Next
End Sub
 
Solution

baqer

New Member
Joined
May 12, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
thank you very mach and best regards for you Because you took your time
the result of last cod that you write is same of first code for all columns I do not know result is true or false I calculate manually and announce
many many thanks for you for help
 

Forum statistics

Threads
1,136,954
Messages
5,678,757
Members
419,782
Latest member
gc75150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top