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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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