CODEWITHSUNDEEP
excel
Creakush Coliko
Creakush Coliko

Reputation: 103

Excel - average, min, max and at 13:00 for each day

On the ''Sheet1'' I have hourly values of temperatures, that looks like this:

Time                 Value
1.1.2017 0:00:00     -5
1.1.2017 1:00:00     -5
1.1.2017 2:00:00     -6
1.1.2017 3:00:00     -7
1.1.2017 4:00:00     -7

and that goes till the end of the year.

On the ''Sheet2'' I need to calculate avg, min, max, and at 13:00 hours for each day, so that needs to look like this:

Day            Avg   Min   Max   13:00
01.01.2017
02.01.2017
03.01.2017
04.01.2017

So I've managed to get it for AVG:

Avg = AVERAGEIFS(Sheet1!B;Sheet1!A;">="&DATE(YEAR(Sheet2!A2);MONTH(Sheet2!A2);DAY(Sheet2!A2));Sheet1!A;"<"&DATE(YEAR(Sheet2!A3);MONTH(Sheet2!A3);DAY(Sheet2!A3)))

But I need help with min, max and especially at 13:00 hours. I was thinking for at "13:00" with vlookup (day&hour;...) but that gives an error, and for min and max maybe something with small and large.

Upvotes: 1

Views: 147

Answers (2)

barry houdini
barry houdini

Reputation: 46341

You can do the average more easily with this formula

=AVERAGEIFS(Sheet1!B:B;Sheet1!A:A;">="&A2;Sheet1!A:A;"<"&A2+1)

For MIN and MAX you can use an array formula like this

=MIN(IF(Sheet1!A:A>=A2;IF(Sheet1!A:A< A2+1;Sheet1!B:B)))

confirmed with CTRL+SHIFT+ENTER

although better to restrict the ranges with the latter

Upvotes: 1

teylyn
teylyn

Reputation: 35915

You can use array formulas like the following:

=AVERAGE(IF(INT(Sheet1!$A$2:$A$1000)=A2;Sheet1!$B$2:$B$1000))
=min(IF(INT(Sheet1!$A$2:$A$1000)=A2;Sheet1!$B$2:$B$1000))
=max(IF(INT(Sheet1!$A$2:$A$1000)=A2;Sheet1!$B$2:$B$1000))

and a lookup for the value at 13:00

=INDEX(Sheet1!B:B;MATCH(A4+TIME(13;0;1);Sheet1!A:A;1))

The first three formulas are array formulas and must be confirmed with Ctrl-Shift-Enter. You should NOT use them with whole column references, because that will slow down your workbook.

A better approach would be to split the date and time into two different columns. Then you can use the super-fast AverageIfs, MaxIfs and MinIfs, which can be used with whole columns with no issue.

Date in column A, time in column B, value in column C

=AVERAGEIFS(Sheet1!C:C;Sheet1!A:A;A2)
=MINIFS(Sheet1!C:C;Sheet1!A:A;A2)
=MAXIFS(Sheet1!C:C;Sheet1!A:A;A2)

And the value for 1300 hours

=SUMIFS(Sheet1!C:C;Sheet1!A:A;A2;Sheet1!B:B;">"&TIME(12;59;59),Sheet1!B:B;"<="&TIME(13;0;1))

The time value lookup is a bit more complex since the exact match would be 0.54166666666666 with an infinite number of 6's at the end. Once you round the last 6 to 7, there is no exact match anymore. Hence the formula looks for a time that is in the time range between 12:59:59 and 13:00:01.

Edit after seeing barryhoudini's post: With newer versions of Excel you should be able to use these non-array formulas with your original layout, so no need for array formulas at all if you use Barry's suggestion for AverageIfs

=MAXIFS(Sheet1!B:B,Sheet1!A:A,">="&A2,Sheet1!A:A,"<="&A2+1)
=MINIFS(Sheet1!B:B,Sheet1!A:A,">="&A2,Sheet1!A:A,"<="&A2+1)

Upvotes: 2

Related Questions