How to calculate avg from various rows

[paulk2002]

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

 

[T. Valko]

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)

 

[paulk2002]

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]

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!