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" <(E-Mail Removed)> wrote in message
news:CF7B8F5B-3F7B-4812-B5D6-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:CF7B8F5B-3F7B-4812-B5D6-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news28DB326-C370-4C87-A713-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:CF7B8F5B-3F7B-4812-B5D6-(E-Mail Removed)...
>> > 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
>> >
>>
>>
>>