Average of rows in range Excel

Question

I have an excel sheet laid out like so:

+--id---+--val--+--timestamp--+
|  01   |  1.2  | 1340769912  |
|-------|-------|-------------|
|  02   |  1.6  | 1340769928  |
+-------+-------+-------------+

I'd like to get the average of values within a range of 60 seconds, or a variance of 60 in the timestamp column. I've tried a bunch of stuff in terms of Excel formulae, etc. but I can't get it to work correctly. Is VBA the way to go?

EDIT: There's a lot of data and a lot of minutes, I need this formula to work if I copy it down a column.

SeanC · Accepted Answer · 2012-06-28 19:45:17Z

4

If you have excel 2007 or later
=AVERAGEIFS($B$1:$B$2,$C$1:$C$2,"<"&C1+60,$C$1:$C$2,">"&C1-60)

for any version of excel:
=AVERAGE(IF(AND($C$1:$C$2<C1+60,$C$1:$C$2>C1-60),$B$1:$B$2)) entered as an array formula using CTRL+SHIFT+ENTER to enter the formula, rather than just ENTER

answered Jun 28, 2012 at 19:45

SeanC

16k5 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Scott Holtzman Over a year ago

+1 I agree. I was on this track, then I thought that OP's time stamp may be date formatted, in which case this would be adding / subtracting 60 days. However, given the text in OP, I think this is spot on.

Peter Kazazes Over a year ago

Tada, works for the first set. How do I use it on a mass scale (5000 entries) without changing the formula each time

SeanC Over a year ago

$C$1:$C$XXXX define your time range, $B$1:$B$XXX define your values to average, and C1 defines the time you are looking at. Point those parts of the formula to whatever you are looking at, and you can copy/paste the formula to wherever you need to

Peter Kazazes Over a year ago

@SeanCheshire It's not performing as I'd like. i.imgur.com/Keh2s.png Column E is the difference from the first time, so in theory the average number should be the same up until the E column hits 60, and it is not. I think the issue lies in the criteria statements: &C1+60 and &C1-60. I'm no expert at this stuff but that's my logical assessment.

SeanC Over a year ago

those give the range around the time point - with +60 and -60, you are looking at up to 60 seconds away from the time inC1. If you want a different range, change the numbers after the + and -.

|

Scott Holtzman · Accepted Answer · 2012-06-28 19:44:38Z

1

Not sure if this is foolproof, but I think it's a good start, and VBA-free.

Picture didn't paste right, formula for Column D should be:

=ABS(TEXT($F$2,"hmmss")-TEXT(C2,"hmmss"))

enter image description here

answered Jun 28, 2012 at 19:44

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Collectives™ on Stack Overflow

Average of rows in range Excel

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related