Averaging Data Set

ZochSteveo · Sunday at 9:23 AM

Hi there,

So I have a list of times at intervals of every 5 minutes for about 7 months or so. For each 5 minute interval, there is a corresponding value for the temperature as well. I need to average the values for all recordings of the temperature for each respective day. Is there a fast way of me being able to do this?

The times are as follows for example: 04/01/2024 00:00, 04/01/2024 00:05, 04/01/2024 00:10 etc. and for each time stamp there is a corresponding temperature value. I need to average each temperature value for the whole day, and do this for every day in which there are around 7 months worth,

Practice Analysis.xlsx

A

B

3

Time

External data

4

1/4/24 0:00

8.703 °C

5

1/4/24 0:05

8.743 °C

6

1/4/24 0:10

8.676 °C

7

1/4/24 0:15

8.664 °C

8

1/4/24 0:20

8.743 °C

9

1/4/24 0:25

8.673 °C

10

1/4/24 0:30

8.596 °C

11

1/4/24 0:35

8.569 °C

12

1/4/24 0:40

8.586 °C

13

1/4/24 0:45

8.637 °C

14

1/4/24 0:50

8.595 °C

15

1/4/24 0:55

8.499 °C

16

1/4/24 1:00

8.340 °C

17

1/4/24 1:05

8.421 °C

18

1/4/24 1:10

8.341 °C

19

1/4/24 1:15

8.353 °C

20

1/4/24 1:20

8.191 °C

21

1/4/24 1:25

8.272 °C

22

1/4/24 1:30

8.090 °C

23

1/4/24 1:35

8.115 °C

24

1/4/24 1:40

8.017 °C

25

1/4/24 1:45

8.082 °C

26

1/4/24 1:50

7.971 °C

27

1/4/24 1:55

7.924 °C

28

1/4/24 2:00

7.880 °C

29

1/4/24 2:05

7.918 °C

30

1/4/24 2:10

7.913 °C

31

1/4/24 2:15

7.835 °C

32

1/4/24 2:20

7.916 °C

33

1/4/24 2:25

7.755 °C

34

1/4/24 2:30

7.925 °C

35

1/4/24 2:35

7.922 °C

36

1/4/24 2:40

7.925 °C

37

1/4/24 2:45

7.980 °C

38

1/4/24 2:50

8.001 °C

39

1/4/24 2:55

7.958 °C

40

1/4/24 3:00

7.989 °C

41

1/4/24 3:05

7.960 °C

42

1/4/24 3:10

7.987 °C

43

1/4/24 3:15

8.070 °C

44

1/4/24 3:20

8.032 °C

45

1/4/24 3:25

8.014 °C

46

1/4/24 3:30

7.933 °C

47

1/4/24 3:35

8.032 °C

48

1/4/24 3:40

7.961 °C

49

1/4/24 3:45

7.978 °C

50

1/4/24 3:50

7.913 °C

51

1/4/24 3:55

7.826 °C

52

1/4/24 4:00

7.735 °C

53

1/4/24 4:05

7.623 °C

54

1/4/24 4:10

7.535 °C

55

1/4/24 4:15

7.411 °C

56

1/4/24 4:20

7.298 °C

57

1/4/24 4:25

7.244 °C

58

1/4/24 4:30

7.142 °C

59

1/4/24 4:35

6.968 °C

60

1/4/24 4:40

6.842 °C

61

1/4/24 4:45

6.744 °C

62

1/4/24 4:50

6.770 °C

63

1/4/24 4:55

6.764 °C

64

1/4/24 5:00

6.715 °C

65

1/4/24 5:05

6.554 °C

66

1/4/24 5:10

6.569 °C

67

1/4/24 5:15

6.602 °C

68

1/4/24 5:20

6.488 °C

69

1/4/24 5:25

6.346 °C

70

1/4/24 5:30

6.369 °C

71

1/4/24 5:35

6.246 °C

72

1/4/24 5:40

6.149 °C

73

1/4/24 5:45

5.969 °C

74

1/4/24 5:50

6.063 °C

75

1/4/24 5:55

5.829 °C

76

1/4/24 6:00

5.838 °C

77

1/4/24 6:05

5.836 °C

78

1/4/24 6:10

5.507 °C

79

1/4/24 6:15

5.703 °C

80

1/4/24 6:20

5.620 °C

81

1/4/24 6:25

5.879 °C

82

1/4/24 6:30

5.696 °C

83

1/4/24 6:35

5.598 °C

84

1/4/24 6:40

5.640 °C

85

1/4/24 6:45

5.683 °C

86

1/4/24 6:50

5.773 °C

87

1/4/24 6:55

5.645 °C

88

1/4/24 7:00

5.612 °C

89

1/4/24 7:05

5.580 °C

90

1/4/24 7:10

5.706 °C

91

1/4/24 7:15

5.972 °C

92

1/4/24 7:20

6.006 °C

93

1/4/24 7:25

6.137 °C

94

1/4/24 7:30

6.129 °C

95

1/4/24 7:35

6.266 °C

96

1/4/24 7:40

6.430 °C

97

1/4/24 7:45

6.545 °C

98

1/4/24 7:50

6.936 °C

99

1/4/24 7:55

7.210 °C

100

1/4/24 8:00

7.282 °C

101

1/4/24 8:05

7.429 °C

102

1/4/24 8:10

7.500 °C

103

1/4/24 8:15

7.636 °C

104

1/4/24 8:20

7.747 °C

105

1/4/24 8:25

8.145 °C

106

1/4/24 8:30

8.344 °C

107

1/4/24 8:35

8.433 °C

108

1/4/24 8:40

8.554 °C

109

1/4/24 8:45

8.763 °C

110

1/4/24 8:50

8.875 °C

111

1/4/24 8:55

9.089 °C

112

1/4/24 9:00

9.266 °C

113

1/4/24 9:05

9.658 °C

114

1/4/24 9:10

9.579 °C

115

1/4/24 9:15

9.749 °C

116

1/4/24 9:20

9.764 °C

117

1/4/24 9:25

9.884 °C

118

1/4/24 9:30

10.028 °C

119

1/4/24 9:35

10.180 °C

120

1/4/24 9:40

10.855 °C

121

1/4/24 9:45

10.938 °C

122

1/4/24 9:50

10.917 °C

123

1/4/24 9:55

11.600 °C

124

1/4/24 10:00

12.050 °C

Sheet1

alansidman · Sunday at 9:52 AM

You only supplied one date's values, however, this Mcode should do what you want. This is a Power Query Solution.

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type date}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "External data", Splitter.SplitTextByPositions({0, 5}, false), {"External data.1", "External data.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"External data.1", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"External data.2"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Time"}, {{"Average", each List.Average([External data.1]), type nullable number}})
in
    #"Grouped Rows"

ZochSteveo · Sunday at 10:00 AM

alansidman said:

You only supplied one date's values, however, this Mcode should do what you want. This is a Power Query Solution.

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type date}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "External data", Splitter.SplitTextByPositions({0, 5}, false), {"External data.1", "External data.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"External data.1", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"External data.2"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Time"}, {{"Average", each List.Average([External data.1]), type nullable number}})
in
    #"Grouped Rows"

Hi,

It would not allow me to add everything in the mini XL2BB sheet, as it can only upload so much and as you can see, this data is expansive. I have not used Power Query before, is there anything I should know?

Many thanks for your time

alansidman · Sunday at 10:04 AM

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

ZochSteveo · Sunday at 10:30 AM

alansidman said:
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

Perfect, thank you.

One last thing, what should I refer to as my Excel table so that it goes to the right thing? At the moment when I try and run the code it says that I cannot do it. What is the name?

Dave Patton · Sunday at 10:49 AM

I added 2 numbers for another date
If your version of Excel has Groupby try that suggestion.

T202503a.xlsm

A

B

C

D

1

Time

External data

2

45295

8.703 °C

45295

7.68250413

3

45295.00347

8.743 °C

45296

11.825

4

45295.00694

8.676 °C

Total

7.74986179

5

45295.01042

8.664 °C

6

45295.01389

8.743 °C

4-Jan-24

7.68250413

7

45295.01736

8.673 °C

5-Jan-24

11.825

8

45295.02083

8.596 °C

2d

Cell Formulas
Range	Formula
C2:D4	C2	=GROUPBY(INT(A2:A124),--LEFT(B2:B124,5),AVERAGE)
D6:D7	D6	=SUM(LEFT($B$2:$B$124,5)*(INT($A$2:$A$124)=G6))/SUM(--(INT($A$2:$A$124)=G6))
Dynamic array formulas.

T202503a.xlsm

C

D

1

Date

Temperature

2

3

2dd

Cell Formulas
Range	Formula
C2:C3	C2	=INT(A2)
D2:D3	D2	=--LEFT(B2,5)

ZochSteveo · Sunday at 10:59 AM

Dave Patton said:
I added 2 numbers for another date
If your version of Excel has Groupby try that suggestion.

T202503a.xlsm
A B C D
1 Time External data
2 45295 8.703 °C 45295 7.68250413
3 45295.00347 8.743 °C 45296 11.825
4 45295.00694 8.676 °C Total 7.74986179
5 45295.01042 8.664 °C
6 45295.01389 8.743 °C 4-Jan-24 7.68250413
7 45295.01736 8.673 °C 5-Jan-24 11.825
8 45295.02083 8.596 °C
2d
Cell Formulas
Range Formula
C2:D4 C2 =GROUPBY(INT(A2:A124),--LEFT(B2:B124,5),AVERAGE)
D6:D7 D6 =SUM(LEFT($B$2:$B$124,5)*(INT($A$2:$A$124)=G6))/SUM(--(INT($A$2:$A$124)=G6))
Dynamic array formulas.

T202503a.xlsm
C D
1 Date Temperature
2 4-Jan-24 8.703
3 4-Jan-24 8.743
2dd
Cell Formulas
Range Formula
C2:C3 C2 =INT(A2)
D2:D3 D2 =--LEFT(B2,5)

Hi there,

This works but I have multiple days to do this on, and it is time consuming to scroll up and down to find the cell number for the starting and end point of each day. What should I do to speed up this process?

Dave Patton · Sunday at 11:04 AM

With the new columns such as C and D that I show above, you can use Excel's built-in feature to provide the Average.

I show just option 1 of the information that is provided by Data|Subtotal
N.B. Excel provides the formulas; do not manually enter formulas.

T202503a.xlsm

C

D

1

Date

Temperature

123

4-Jan-24

7.68250413

126

5-Jan-24

11.825

127

Grand Average

7.74986179

128

2dd

Cell Formulas
Range	Formula
D123	D123	=SUBTOTAL(1,D2:D122)
D126	D126	=SUBTOTAL(1,D124:D125)
D127	D127	=SUBTOTAL(1,D2:D125)

Dave Patton · Sunday at 11:09 AM

Just list the relevant dates.
- list the required dates or
- prepare a unique list of dates

What data do you have and what type of summary do you require?

Does Excel 2024 have Groupby?

ZochSteveo · Sunday at 11:11 AM

Dave Patton said:
With the new columns such as C and D that I show above, you can use Excel's built-in feature to provide the Average.

I show just option 1 of the information that is provided by Data|Subtotal
N.B. Excel provides the formulas; do not manually enter formulas.

T202503a.xlsm
C D
1 Date Temperature
123 4-Jan-24 7.68250413
126 5-Jan-24 11.825
127 Grand Average 7.74986179
128
2dd
Cell Formulas
Range Formula
D123 D123 =SUBTOTAL(1,D2:D122)
D126 D126 =SUBTOTAL(1,D124:D125)
D127 D127 =SUBTOTAL(1,D2:D125)

Hi David,

I think I have confused this a touch. The data that I uploaded was just a fraction of the temperatures that were available for that one day. I could not add all of them as there was a restriction on the amount of data that I could upload. Anyway, the data continues for the rest of that day, then the next, and the next and so on. What I need is to calculate the average temperature for each individual day. So, for example, the average temperature for each data point from 00:00 hours on 31/03/2024 until 23:55 hours 31/03/2024, and then the same for the next day, and the next. I hope this makes sense, as I am not sure that code does exactly what I am after.

Averaging Data Set

ZochSteveo

New Member

alansidman

Well-known Member

ZochSteveo

New Member

alansidman

Well-known Member

The Complete Guide to Installing Power Query - Excel Campus

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Power Query - How To Paste Code (video) - Excel Solutions

ZochSteveo

New Member

The Complete Guide to Installing Power Query - Excel Campus

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Power Query - How To Paste Code (video) - Excel Solutions

Dave Patton

Well-known Member

ZochSteveo

New Member

Dave Patton

Well-known Member

Dave Patton

Well-known Member

ZochSteveo

New Member

Similar threads

Share this page

Averaging Data Set

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock