=AVERAGE(--(B4:B18000=39),--(B4:B18000<=47,?????)

In D4 enter: 39
In E4 enter: 47

F4:

=AVERAGE(IF($B$4:$B$18000=39,IF($B$4:$B$18000<=47 ,$D$4:$D$18000)))

which you need to confirm with control+shift+enter instead of just enter.

srf99 wrote:
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.



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.