This is A LOT harder than I thought it would be

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> skrev i melding
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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" <[email protected]> skrev i melding
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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.

Originally Posted by SailFL

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" <[email protected]> skrev i melding
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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 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" <[email protected]> skrev i melding
news:[email protected]...
> 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" <[email protected]> skrev i melding
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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),ArousalState))

--
(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 <[email protected]>, 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 <[email protected]>,
[email protected]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),ArousalState))
>
> --
> (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 <[email protected]>, 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}