I am trying todo something which I "thought" would be simple.

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.

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.
--
RSF

Hi!

Column A = decimal values
Column B = integer values

D1 = 1
E1 = 1.3

=SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),B1:B5)

Biff

"Robert" wrote in message
...
I am trying todo something which I "thought" would be simple.

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.

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.
--
RSF

This doesn't make any snese. How do I put this formula in a macro. Please
explain. Thankks.
--
RSF


"Biff" wrote:

Hi!

Column A = decimal values
Column B = integer values

D1 = 1
E1 = 1.3

=SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),B1:B5)

Biff

"Robert" wrote in message
...
I am trying todo something which I "thought" would be simple.

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.

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.
--
RSF

Sorry, can't help with a macro. But look how easy it is using worksheet
functions!

Biff

"Robert" wrote in message
...
This doesn't make any snese. How do I put this formula in a macro.
Please
explain. Thankks.
--
RSF


"Biff" wrote:

Hi!

Column A = decimal values
Column B = integer values

D1 = 1
E1 = 1.3

=SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),B1:B5)

Biff

"Robert" wrote in message
...
I am trying todo something which I "thought" would be simple.

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.

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.
--
RSF

It makes perfect sense, you're just too ignorant to implement it. If your
manners weren't as bad as your spelling then someone might consider helping
you with it.

"Robert" skrev i melding
...
This doesn't make any snese. How do I put this formula in a macro.
Please
explain. Thankks.
--
RSF


"Biff" wrote:

Hi!

Column A = decimal values
Column B = integer values

D1 = 1
E1 = 1.3

=SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),B1:B5)

Biff

"Robert" wrote in message
...
I am trying todo something which I "thought" would be simple.

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.

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.
--
RSF

The only one with bad manners here is you, Harald. Because some one doesn't
know how to do some thing or doesn't understand is not a problem here. That
is the purpose of these message boards. So he can't type big deal. I bet
you didn't always know everthing!
--
SailFL


"Harald Staff" wrote:

It makes perfect sense, you're just too ignorant to implement it. If your
manners weren't as bad as your spelling then someone might consider helping
you with it.

"Robert" skrev i melding
...
This doesn't make any snese. How do I put this formula in a macro.
Please
explain. Thankks.
--
RSF


"Biff" wrote:

Hi!

Column A = decimal values
Column B = integer values

D1 = 1
E1 = 1.3

=SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),B1:B5)

Biff

"Robert" wrote in message
...
I am trying todo something which I "thought" would be simple.

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.

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.
--
RSF

Robert,

To answer your original question, and not being well up on VBA code, I
imagine that you would start with two zero totals, then run through
each cell of your range testing and adding to either total as required,
then save the two totals into cells, however, this is a waste of effort,
to do what Excel already does.

The formula shown by Biff can be copied straight into the cell in which
you want the total to appear,
in D1 put a 1
in E1 put 1.3
in F1 put 10
into the cell you want the total of 1 to 1.3 range put
=SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),B1:B5)

and into the cell you want the total of what is over 1.3 and under 10
put
=SUMPRODUCT(--(A1:A5=E1),--(A1:A5<=F1),B1:B5)

and no VBA code is required.

Looking at your request I think it possible that you could have used:
=SUMif(A1:A5,"<=1.3",B1:B5)
=SUMif(A1:A5,"1.3",1:B5)

but the Sumproduct as shown will achieve more variations for future
needs.

Hope this helps.


SailFL Wrote:
The only one with bad manners here is you, Harald. Because some one
doesn't
know how to do some thing or doesn't understand is not a problem here.
That
is the purpose of these message boards. So he can't type big deal. I
bet
you didn't always know everthing!
--
SailFL


"Harald Staff" wrote:

It makes perfect sense, you're just too ignorant to implement it. If
your
manners weren't as bad as your spelling then someone might consider
helping
you with it.

"Robert" skrev i melding
...
This doesn't make any snese. How do I put this formula in a
macro.
Please
explain. Thankks.
--
RSF


"Biff" wrote:

Hi!

Column A = decimal values
Column B = integer values

D1 = 1
E1 = 1.3

=SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),B1:B5)

Biff

"Robert" wrote in message
...
I am trying todo something which I "thought" would be simple.

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.

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.
--
RSF








--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=397419

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.

Imagine you spent about an hour writing and testing a solution for someones
problem, all for free and good will. This person doesn't immediately
understand it, so he speed-types "this doesn't make any snese" and click
Send. Your response would of course be "this nice person with the great
manners really shows me the respect I deserve".

"SailFL" skrev i melding
...
The only one with bad manners here is you, Harald. Because some one
doesn't
know how to do some thing or doesn't understand is not a problem here.
That
is the purpose of these message boards. So he can't type big deal. I bet
you didn't always know everthing!
--
SailFL


"Harald Staff" wrote:

It makes perfect sense, you're just too ignorant to implement it. If
your
manners weren't as bad as your spelling then someone might consider
helping
you with it.

"Robert" skrev i melding
...
This doesn't make any snese. How do I put this formula in a macro.
Please
explain. Thankks.
--
RSF


"Biff" wrote:

Hi!

Column A = decimal values
Column B = integer values

D1 = 1
E1 = 1.3

=SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),B1:B5)

Biff

"Robert" wrote in message
...
I am trying todo something which I "thought" would be simple.

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.

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.
--
RSF

I appreciate the help anyway Biff. Thanks.
--
RSF


"Biff" wrote:

Sorry, can't help with a macro. But look how easy it is using worksheet
functions!

Biff

"Robert" wrote in message
...
This doesn't make any snese. How do I put this formula in a macro.
Please
explain. Thankks.
--
RSF


"Biff" wrote:

Hi!

Column A = decimal values
Column B = integer values

D1 = 1
E1 = 1.3

=SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),B1:B5)

Biff

"Robert" wrote in message
...
I am trying todo something which I "thought" would be simple.

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.

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.
--
RSF

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.

=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.

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.

Correction...

If cell B1 is a column header, the named formula should be:

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


--
Regards,

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

In article ,
hEm says...
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

{snip}