Hello! Pls excuse if this is a repost. I was logged in, and after I clicked
on Post, the stupid thing wanted me to verify my email addr and my identitiy
again, and I had to log back in, so I'm posting again - ARGH.


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

It sounds like you're using autofilter?

If so, use the SUBTOTAL function:

=SUBTOTAL(1,G5:G2600)

Make sure you put the formula in a row that is outside of the filtered area!
I always put subtotals above the data.

Or, you can try this array formula** :

Assume times are in column B:

=AVERGE(IF(HOUR(B5:B2600)=22,G5:G2600))

It'll be a little different for 12:00 AM (00:00)** :

=AVERGE(IF((ISNUMBER(B5:B2600))*(HOUR(B5:B2600)=0) ,G5:G2600))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"paulk2002" wrote in message
...
Hello! Pls excuse if this is a repost. I was logged in, and after I
clicked
on Post, the stupid thing wanted me to verify my email addr and my
identitiy
again, and I had to log back in, so I'm posting again - ARGH.


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

Thank you T Valko (Biff?). I saw Bob's answer first, and that did the trick.
I'm not sure I understand all the items you have in your answer, but at least
I got it to work with Bob's answer, and I appreciate you supplying one too!
I have copied it and will try it out later, for in working with Bob's answer,
I can't just copy the formula and paste it into other worksheets. I'm finding
I have to edit each and every line in each worksheet, otherwise I get a
Value, or other errors, and that's very time consuming, for there's 24 hours
in each worksheet, and I have 25 worksheets to do :(

Thanks again!

Regards,
Paul

"T. Valko" wrote:

It sounds like you're using autofilter?

If so, use the SUBTOTAL function:

=SUBTOTAL(1,G5:G2600)

Make sure you put the formula in a row that is outside of the filtered area!
I always put subtotals above the data.

Or, you can try this array formula** :

Assume times are in column B:

=AVERGE(IF(HOUR(B5:B2600)=22,G5:G2600))

It'll be a little different for 12:00 AM (00:00)** :

=AVERGE(IF((ISNUMBER(B5:B2600))*(HOUR(B5:B2600)=0) ,G5:G2600))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"paulk2002" wrote in message
...
Hello! Pls excuse if this is a repost. I was logged in, and after I
clicked
on Post, the stupid thing wanted me to verify my email addr and my
identitiy
again, and I had to log back in, so I'm posting again - ARGH.


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

I saw Bob's answer first, and that did the trick.

I don't see another reply to this thread. If you have something that works
that's all that counts!


--
Biff
Microsoft Excel MVP


"paulk2002" wrote in message
...
Thank you T Valko (Biff?). I saw Bob's answer first, and that did the
trick.
I'm not sure I understand all the items you have in your answer, but at
least
I got it to work with Bob's answer, and I appreciate you supplying one
too!
I have copied it and will try it out later, for in working with Bob's
answer,
I can't just copy the formula and paste it into other worksheets. I'm
finding
I have to edit each and every line in each worksheet, otherwise I get a
Value, or other errors, and that's very time consuming, for there's 24
hours
in each worksheet, and I have 25 worksheets to do :(

Thanks again!

Regards,
Paul

"T. Valko" wrote:

It sounds like you're using autofilter?

If so, use the SUBTOTAL function:

=SUBTOTAL(1,G5:G2600)

Make sure you put the formula in a row that is outside of the filtered
area!
I always put subtotals above the data.

Or, you can try this array formula** :

Assume times are in column B:

=AVERGE(IF(HOUR(B5:B2600)=22,G5:G2600))

It'll be a little different for 12:00 AM (00:00)** :

=AVERGE(IF((ISNUMBER(B5:B2600))*(HOUR(B5:B2600)=0) ,G5:G2600))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"paulk2002" wrote in message
...
Hello! Pls excuse if this is a repost. I was logged in, and after I
clicked
on Post, the stupid thing wanted me to verify my email addr and my
identitiy
again, and I had to log back in, so I'm posting again - ARGH.


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