CODEWITHSUNDEEP
excel
l3win
l3win

Reputation: 235

Average by excluding data around a certain time during day in Excel

I have one hour measurement data for a whole year. The timespamp is in column A.

I am trying to find a simple way to average the data by excluding all points at 6,7, and 8 AM. However, since these points pop up 365 times in the data, I am having problems with applying functions like averageifs etc.

Is there another way to do this?

Here's an example of two columns, timestamp in the first column and data in the 2nd.

10/25/13 0:00   186.7583777
10/25/13 1:00   181.5850511
10/25/13 2:00   182.5474936
10/25/13 3:00   182.7110036
10/25/13 4:00   182.9423479
10/25/13 5:00   183.4864833
10/25/13 6:00   182.4676709
10/25/13 7:00   181.2562128
10/25/13 8:00   183.1540795

Upvotes: 0

Views: 310

Answers (3)

barry houdini
barry houdini

Reputation: 46341

An array formula like this will also work

=AVERAGE(IF((HOUR(A2:A1000)<6)+(HOUR(A2:A1000)>8),IF(A2:A1000<>"",B2:B1000)))

CTRL+SHIFT+ENTER

adjust ranges as required but preferable not to use whole columns in this formula

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Try this:

=SUMPRODUCT((HOUR(A1:A8784)<>6)*(HOUR(A1:A8784)<>7)*(HOUR(A1:A8784)<>8)*B1:B8784)/SUMPRODUCT((HOUR(A1:A8784)<>6)*(HOUR(A1:A8784)<>7)*(HOUR(A1:A8784)<>8)*(B1:B8784<>""))

The range references were chosen since a 366 day year has 8784 hours; you might need to change them if your data doesn't start in A1; or if you are averaging more than one year in a column.

Upvotes: 1

PermaNoob
PermaNoob

Reputation: 869

You could do it with an array formula, but I would probably put this formula in column C: =if(or(hour(A:A)<6,hour(A:A)>8),B:B,"") Drag that formula all the way down, then put this formula in cell D1 =average(C:C) If you want to look at other times, just change the formula in column C.

Upvotes: 1

Related Questions