No need for VBA.

Aladin was on the right track but somehow didn't quite finish it
correctly.

OK, you have data in B and D starting with, let's say, row 2. Suppose
in E2 you put in one start time value and in F2 you put in the
corresponding end value. If the *total* number of rows is
predetermined, all you need is the *array formula* (1) entered in any
cell, say G2:

=AVERAGE(IF(($B$2:$B$28=E2)*($B$2:$B$28<=F2),$D$2 :$D$28))

In my test the data range was 2:28. Note the use of absolute and
relative cell addresses ($x is an absolute row/column address; the
absence of the $ makes it relative).

Now, you can enter a different set of start and stop times in E3 and F3
respectively. Copy the formula in G2 to G3 and you will have the
correct results for this set of start-stop values.

You can continue down E:G as far as desired. Just make sure you have
the absolute/relative addresses correct.

If the number of rows of data can change, use a named formula. Suppose
the data are laid out as above and row B1 has some kind of a column
header. Then, create two names (Insert | Name Define...)

TimeVals =OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B:$B),1)
ArousalState =OFFSET(TimeVals,0,2)

and change the G2 formula to the array formula:
=AVERAGE(IF((TimeVals=E2)*(TimeVals<=F2),ArousalS tate))

--
(1) An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , srf99
@discussions.microsoft.com says...
Hi There,

I'm the sister of the original poster and thought that I would clarify
things a bit. I've read the above posts and am not sure they answer the
exact question we have. Part of that may have to do with my brother
describing the problem poorly.

I have two columns I am interested in. The first column, column B, consists
of ascending (0 to 77) decimal numbers. Those numbers represent minutes.
Column D contains numbers that can randomly fluctuate. Those numbers are
also decimals and correspond to someone's physiological arousal (typically
the numbers range from -3 to +3).

Without having to go through and hand select sections of data, I would like
to be able to have Excel scroll through Column B (minutes) and average the
physiological arousal (Column D) for a given time period (say minutes 39-47).


Biff wrote the following formula earlier.
=SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),B1:B5)

I attemped to modify it using the above problem:
=AVERAGE(--(B4:B18000=39),--(B4:B18000<=47,?????)

The question marks represent where my confusion sets in. I do not want to
have to specify the cell range for the formula to average. I want Excel to
determine the cell range and then average those cells. Keep in mind that I
have over 100 of these to do and they all differ. So I want to be able to
specify the minute range only and have Excel do the rest. Does that make
sense? Any help is greatly appreciated.

Thanks,
Stephanie

"Harlan Grove" wrote:

Robert wrote...
....
I have a column of number from 0 through 77. I have another column beside
of it. The numbers are like this:

1 5
1.1 6
1.3 4
1.35 5
1.5 6

All the way to 77.

The column on the left starts with 1, not 0. Is the first column sorted
in ascending order?

I am trying to write a code in VBA that says, basically, if the value in
this cell is between (for instance, 1 and 1.3 then sum the values from the
corresponding column. The answer to the above would be 15. ELSE, if the
values are between 1.35 and 1.5, then sum the values in the corresponding
column (the answer being 11) and so on. I wanted to store the answer values
in specified cells on the same worksheet. Can someone please help? Thanks.

Why use VBA rather than worksheet formulas?

If you must use VBA, and *IF* the first column is sorted in ascending
order, try

Sub foobar()
Dim p As Long, q As Long
If Not TypeOf Selection Is Range Then Exit Sub
If Selection.Columns.Count < 2 Then Exit Sub
With Application.WorksheetFunction
p = .Match(1, Selection.Columns(1))
If Selection.Cells(p, 1) = 1 Then p = p - 1
q = .Match(1.3, Selection.Columns(1))
If p <= q Then _
Selection.Offset(0, 2).Resize(1, 1).Value = _
.Sum(Selection.Offset(p, 1).Resize(q - p, 1))
End With
End Sub

which puts the condition sum you're seeking into the cell just to the
right of the first row of your selected 2 column range.