Hello!

I've been searching for hours and just can't find what I need. I found many
articles from Biff, but haven't found examples that meet my criteria and
scenario, so I'm turning to you all, 'cuz I believe there's a solution out
there somewhere!

I'm not too experienced with Excel and formula's, nor the names of some of
the items/examples I saw, but if given some things to try, I will. Thanks in
advance for the assistance

I have csv files that convert into excel, of which there are columns with
the date, time, and certain #'s in many other columns. I am only interested
in the avg from one particular column - let's say it's column G. There are
about 2600 rows currently, and will grow over time.

Each row has a different time, but it's based on the 24 hr clock/time. For
example, row 5 starts with 00:00, row 6 has 01:00, row 7 has 02:00, row 12 is
07:00, row 18 is 13:00, row 28 is 23:00, and then row 29 starts 00:00 again,
and keeps doing that, for the most part. Some rows skip some times due to
other things.

I know how to get the avg from all the times of/over all of the 2600 rows
right now, for column G - go to the AutoSum, highlight all the rows in that
column, and no pblm.

I can also sort the times, so for example, if I only want to see all the
times that are 22:00, I can see that, and the #'s for those times show up in
column G, which they should.

However, since the time of 22:00 isn't in rows (for example) 20, 21, 22, but
rather 20, 44, 68, etc., (since it's a 24 hr clock), when I use the AutoSum,
and just select the rows that show up in column G, just for the time of
22:00, the #'s shown are as follows: (G3:G2600), and I do NOT want that. I
was expecting to see just the rows that have the time of 22:00, but it showed
me ALL rows.

I also found that I can do a special avg (or something to that effect),
where I can manually put in up to 30 different rows, and get the avg for
those rows, but the option won't let me put in more than 30 rows, and I don't
want to have to do that for all the rows with just a certain time that I want
to select - it would take way too long, even if the option would allow up to
1000 rows, for example.

Example of what I see after filtering for a particular time, that I then am
trying to get the average of column G for all rows shown for time 22:00:

Row # Time #'s I'm trying to get avg from, in Column G
20 22:00 10
44 22:00 38
68 22:00 18

and so on....

I don't know how to create a formula to get the avg for just the rows that
have a certain time in it. Can it be done, and if so, how??? Please provide
exact syntax, 'cuz that's what I will put in, and try.

Again, thanks in advance for your assistance!

Regards,
Paul

=AVERAGE(IF(B1:B100=--"22:00",C1:C1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

This assumes the time is in column B, the value to average is in column C

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"paulk2002" <(E-Mail Removed)> wrote in message
news:43845CB0-6710-4D5F-8FF1-(E-Mail Removed)...
> Hello!
>
> I've been searching for hours and just can't find what I need. I found
> many
> articles from Biff, but haven't found examples that meet my criteria and
> scenario, so I'm turning to you all, 'cuz I believe there's a solution out
> there somewhere!
>
> I'm not too experienced with Excel and formula's, nor the names of some of
> the items/examples I saw, but if given some things to try, I will. Thanks
> in
> advance for the assistance
>
> I have csv files that convert into excel, of which there are columns with
> the date, time, and certain #'s in many other columns. I am only
> interested
> in the avg from one particular column - let's say it's column G. There are
> about 2600 rows currently, and will grow over time.
>
> Each row has a different time, but it's based on the 24 hr clock/time. For
> example, row 5 starts with 00:00, row 6 has 01:00, row 7 has 02:00, row 12
> is
> 07:00, row 18 is 13:00, row 28 is 23:00, and then row 29 starts 00:00
> again,
> and keeps doing that, for the most part. Some rows skip some times due to
> other things.
>
> I know how to get the avg from all the times of/over all of the 2600 rows
> right now, for column G - go to the AutoSum, highlight all the rows in
> that
> column, and no pblm.
>
> I can also sort the times, so for example, if I only want to see all the
> times that are 22:00, I can see that, and the #'s for those times show up
> in
> column G, which they should.
>
> However, since the time of 22:00 isn't in rows (for example) 20, 21, 22,
> but
> rather 20, 44, 68, etc., (since it's a 24 hr clock), when I use the
> AutoSum,
> and just select the rows that show up in column G, just for the time of
> 22:00, the #'s shown are as follows: (G3:G2600), and I do NOT want that.
> I
> was expecting to see just the rows that have the time of 22:00, but it
> showed
> me ALL rows.
>
> I also found that I can do a special avg (or something to that effect),
> where I can manually put in up to 30 different rows, and get the avg for
> those rows, but the option won't let me put in more than 30 rows, and I
> don't
> want to have to do that for all the rows with just a certain time that I
> want
> to select - it would take way too long, even if the option would allow up
> to
> 1000 rows, for example.
>
> Example of what I see after filtering for a particular time, that I then
> am
> trying to get the average of column G for all rows shown for time 22:00:
>
> Row # Time #'s I'm trying to get avg from, in Column G
> 20 22:00 10
> 44 22:00 38
> 68 22:00 18
>
> and so on....
>
> I don't know how to create a formula to get the avg for just the rows that
> have a certain time in it. Can it be done, and if so, how??? Please
> provide
> exact syntax, 'cuz that's what I will put in, and try.
>
> Again, thanks in advance for your assistance!
>
> Regards,
> Paul
>

Thanks Bob, it works perfectly! I've noticed tho that if I copy and paste a
section from one worksheet to another, I have to edit each line, and then do
the ctrl, shift and enter after editing, and that's very time consuming, for
I have to do that for 24 hours on each worksheet, and I have 25 worksheets to
do it on, but at least it works, and I didn't know how to get that
accomplished Thanks again!

Regards,
Paul

"Bob Phillips" wrote:

> =AVERAGE(IF(B1:B100=--"22:00",C1:C1000))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
> Excel will automatically enclose the formula in braces (curly brackets), do
> not try to do this manually.
> When editing the formula, it must again be array-entered.
>
> This assumes the time is in column B, the value to average is in column C
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "paulk2002" <(E-Mail Removed)> wrote in message
> news:43845CB0-6710-4D5F-8FF1-(E-Mail Removed)...
> > Hello!
> >
> > I've been searching for hours and just can't find what I need. I found
> > many
> > articles from Biff, but haven't found examples that meet my criteria and
> > scenario, so I'm turning to you all, 'cuz I believe there's a solution out
> > there somewhere!
> >
> > I'm not too experienced with Excel and formula's, nor the names of some of
> > the items/examples I saw, but if given some things to try, I will. Thanks
> > in
> > advance for the assistance
> >
> > I have csv files that convert into excel, of which there are columns with
> > the date, time, and certain #'s in many other columns. I am only
> > interested
> > in the avg from one particular column - let's say it's column G. There are
> > about 2600 rows currently, and will grow over time.
> >
> > Each row has a different time, but it's based on the 24 hr clock/time. For
> > example, row 5 starts with 00:00, row 6 has 01:00, row 7 has 02:00, row 12
> > is
> > 07:00, row 18 is 13:00, row 28 is 23:00, and then row 29 starts 00:00
> > again,
> > and keeps doing that, for the most part. Some rows skip some times due to
> > other things.
> >
> > I know how to get the avg from all the times of/over all of the 2600 rows
> > right now, for column G - go to the AutoSum, highlight all the rows in
> > that
> > column, and no pblm.
> >
> > I can also sort the times, so for example, if I only want to see all the
> > times that are 22:00, I can see that, and the #'s for those times show up
> > in
> > column G, which they should.
> >
> > However, since the time of 22:00 isn't in rows (for example) 20, 21, 22,
> > but
> > rather 20, 44, 68, etc., (since it's a 24 hr clock), when I use the
> > AutoSum,
> > and just select the rows that show up in column G, just for the time of
> > 22:00, the #'s shown are as follows: (G3:G2600), and I do NOT want that.
> > I
> > was expecting to see just the rows that have the time of 22:00, but it
> > showed
> > me ALL rows.
> >
> > I also found that I can do a special avg (or something to that effect),
> > where I can manually put in up to 30 different rows, and get the avg for
> > those rows, but the option won't let me put in more than 30 rows, and I
> > don't
> > want to have to do that for all the rows with just a certain time that I
> > want
> > to select - it would take way too long, even if the option would allow up
> > to
> > 1000 rows, for example.
> >
> > Example of what I see after filtering for a particular time, that I then
> > am
> > trying to get the average of column G for all rows shown for time 22:00:
> >
> > Row # Time #'s I'm trying to get avg from, in Column G
> > 20 22:00 10
> > 44 22:00 38
> > 68 22:00 18
> >
> > and so on....
> >
> > I don't know how to create a formula to get the avg for just the rows that
> > have a certain time in it. Can it be done, and if so, how??? Please
> > provide
> > exact syntax, 'cuz that's what I will put in, and try.
> >
> > Again, thanks in advance for your assistance!
> >
> > Regards,
> > Paul
> >
>
>
>

Maybe this would work better

=AVERAGE(IF($B$1:$B$100=--"22:00",$C$1:$C$1000))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"paulk2002" <(E-Mail Removed)> wrote in message
news:53432C62-2482-4C43-8208-(E-Mail Removed)...
> Thanks Bob, it works perfectly! I've noticed tho that if I copy and paste
> a
> section from one worksheet to another, I have to edit each line, and then
> do
> the ctrl, shift and enter after editing, and that's very time consuming,
> for
> I have to do that for 24 hours on each worksheet, and I have 25 worksheets
> to
> do it on, but at least it works, and I didn't know how to get that
> accomplished Thanks again!
>
> Regards,
> Paul
>
> "Bob Phillips" wrote:
>
>> =AVERAGE(IF(B1:B100=--"22:00",C1:C1000))
>>
>> which is an array formula, it should be committed with Ctrl-Shift-Enter,
>> not
>> just Enter.
>> Excel will automatically enclose the formula in braces (curly brackets),
>> do
>> not try to do this manually.
>> When editing the formula, it must again be array-entered.
>>
>> This assumes the time is in column B, the value to average is in column C
>>
>> --
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> "paulk2002" <(E-Mail Removed)> wrote in message
>> news:43845CB0-6710-4D5F-8FF1-(E-Mail Removed)...
>> > Hello!
>> >
>> > I've been searching for hours and just can't find what I need. I found
>> > many
>> > articles from Biff, but haven't found examples that meet my criteria
>> > and
>> > scenario, so I'm turning to you all, 'cuz I believe there's a solution
>> > out
>> > there somewhere!
>> >
>> > I'm not too experienced with Excel and formula's, nor the names of some
>> > of
>> > the items/examples I saw, but if given some things to try, I will.
>> > Thanks
>> > in
>> > advance for the assistance
>> >
>> > I have csv files that convert into excel, of which there are columns
>> > with
>> > the date, time, and certain #'s in many other columns. I am only
>> > interested
>> > in the avg from one particular column - let's say it's column G. There
>> > are
>> > about 2600 rows currently, and will grow over time.
>> >
>> > Each row has a different time, but it's based on the 24 hr clock/time.
>> > For
>> > example, row 5 starts with 00:00, row 6 has 01:00, row 7 has 02:00, row
>> > 12
>> > is
>> > 07:00, row 18 is 13:00, row 28 is 23:00, and then row 29 starts 00:00
>> > again,
>> > and keeps doing that, for the most part. Some rows skip some times due
>> > to
>> > other things.
>> >
>> > I know how to get the avg from all the times of/over all of the 2600
>> > rows
>> > right now, for column G - go to the AutoSum, highlight all the rows in
>> > that
>> > column, and no pblm.
>> >
>> > I can also sort the times, so for example, if I only want to see all
>> > the
>> > times that are 22:00, I can see that, and the #'s for those times show
>> > up
>> > in
>> > column G, which they should.
>> >
>> > However, since the time of 22:00 isn't in rows (for example) 20, 21,
>> > 22,
>> > but
>> > rather 20, 44, 68, etc., (since it's a 24 hr clock), when I use the
>> > AutoSum,
>> > and just select the rows that show up in column G, just for the time of
>> > 22:00, the #'s shown are as follows: (G3:G2600), and I do NOT want
>> > that.
>> > I
>> > was expecting to see just the rows that have the time of 22:00, but it
>> > showed
>> > me ALL rows.
>> >
>> > I also found that I can do a special avg (or something to that effect),
>> > where I can manually put in up to 30 different rows, and get the avg
>> > for
>> > those rows, but the option won't let me put in more than 30 rows, and I
>> > don't
>> > want to have to do that for all the rows with just a certain time that
>> > I
>> > want
>> > to select - it would take way too long, even if the option would allow
>> > up
>> > to
>> > 1000 rows, for example.
>> >
>> > Example of what I see after filtering for a particular time, that I
>> > then
>> > am
>> > trying to get the average of column G for all rows shown for time
>> > 22:00:
>> >
>> > Row # Time #'s I'm trying to get avg from, in Column G
>> > 20 22:00 10
>> > 44 22:00 38
>> > 68 22:00 18
>> >
>> > and so on....
>> >
>> > I don't know how to create a formula to get the avg for just the rows
>> > that
>> > have a certain time in it. Can it be done, and if so, how??? Please
>> > provide
>> > exact syntax, 'cuz that's what I will put in, and try.
>> >
>> > Again, thanks in advance for your assistance!
>> >
>> > Regards,
>> > Paul
>> >
>>
>>
>>