Macro to average specific times during a month

[ferretydeath]

I am looking for a macro that will allow me to find the mean for all of a specific time during a month. For Example, I want to find the average percentage of capacity for every day at 11:00 am for the month of January.

thanks.

P.S. for info purposes, the columns below are from columns G,H, and I of my spreadsheet.


Percentage of Capacity By Hour Hour Month/Day/Year
0.00% 11:00 1/22/2007
0.00% 12:00 1/22/2007
0.00% 13:00 1/22/2007
0.00% 14:00 1/22/2007
0.00% 15:00 1/22/2007
0.00% 16:00 1/22/2007
0.00% 17:00 1/22/2007
0.00% 18:00 1/22/2007
0.00% 19:00 1/22/2007
0.00% 20:00 1/22/2007
0.76% 21:00 1/22/2007
1.59% 22:00 1/22/2007
1.38% 23:00 1/22/2007
2.68% 0:00 1/23/2007
1.75% 1:00 1/23/2007
3.65% 2:00 1/23/2007
3.40% 3:00 1/23/2007
2.41% 4:00 1/23/2007

[NBVC]

Cross posted here: http://www.ozgrid.com/forum/showthread.php?t=89708

Please read our forum rules about crossposting

[Richard Buttrey]

I am looking for a macro that will allow me to find the mean for all of a specific time during a month. For Example, I want to find the average percentage of capacity for every day at 11:00 am for the month of January.

thanks.

P.S. for info purposes, the columns below are from columns G,H, and I of my spreadsheet.


Percentage of Capacity By Hour Hour Month/Day/Year
0.00% 11:00 1/22/2007
0.00% 12:00 1/22/2007
0.00% 13:00 1/22/2007
0.00% 14:00 1/22/2007
0.00% 15:00 1/22/2007
0.00% 16:00 1/22/2007
0.00% 17:00 1/22/2007
0.00% 18:00 1/22/2007
0.00% 19:00 1/22/2007
0.00% 20:00 1/22/2007
0.76% 21:00 1/22/2007
1.59% 22:00 1/22/2007
1.38% 23:00 1/22/2007
2.68% 0:00 1/23/2007
1.75% 1:00 1/23/2007
3.65% 2:00 1/23/2007
3.40% 3:00 1/23/2007
2.41% 4:00 1/23/2007

Why bother with a macro. Just use

Please Login or Register  to view this content.

Assumes data starts on row 1.

HTH

[ferretydeath]

that sure looks neat but it doesn't work, even when I change the values to match my spreadsheet... any idea why?

[Richard Buttrey]

that sure looks neat but it doesn't work, even when I change the values to match my spreadsheet... any idea why?

If your data is contained within columns G:I as your OP suggested, specifically within rows 1:744, then there seems no reason why it shouldn't work.

When you say you've changed the values, what exactly have you changed?

Can you attach your workbook here so I can take a look?

Rgds

[ferretydeath]

my columns are contained between G:I

my rows are contained within 2:8785 with 1 being the header row...

I would love to attach my workbook but its too big for the website to accept it.

[Richard Buttrey]

my columns are contained between G:I

my rows are contained within 2:8785 with 1 being the header row...

I would love to attach my workbook but its too big for the website to accept it.

Hi,

Since you have labels in row 1, change the formula such that the start of each range is G2, H2, I2 etc. i.e. they cover the data and not the text labels.

That should sort it.

Rgds

[ferretydeath]

thanks for being so persistent but I have tried everything with the values (including everything you mentioned multiple times, and I can't get this formula to work...

the first value on j2 is 0
and every value past that is Div/0

=SUMPRODUCT((H1:H744=11/24)*(G1:G744)*(MONTH(I1:I744)=1)/((COUNTIF(H1:H744,11/24))))
I've tried changing the first character to h2-g2-i2-h2
I've tried changed the last character,

what am I doing wrong?

[Richard Buttrey]

thanks for being so persistent but I have tried everything with the values (including everything you mentioned multiple times, and I can't get this formula to work...

the first value on j2 is 0
and every value past that is Div/0

=SUMPRODUCT((H1:H744=11/24)*(G1:G744)*(MONTH(I1:I744)=1)/((COUNTIF(H1:H744,11/24))))
I've tried changing the first character to h2-g2-i2-h2
I've tried changed the last character,

what am I doing wrong?

On the face of it, nothing. The fact that you are getting a Div/0 though means that the divisor bit that contains the COUNTIF() function is returning zero, That can only be the case if there are no 11:00 times in column H. Can you confirm that?

If you think there are some 11:00 times, maybe they are just text which looks like 11:00, and not a proper Excel Time number. Please check by temporarily formatting one of the 11:00 'times' to a number. It should show as 0.458333333333333, which is the time number for 11 o'clock, i.e. 11/24.

If this isn't the answer could you zip up your workbook if you think it's too big and attach it, or save a copy with most of the rows deleted, just leaving some sample rows and attach that.

Rgds