Remember Me?

#1
May 12th 09, 02:32 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: May 2009 Posts: 8
hourly average help

I need help in trying to set up a work sheet that will keep track of an
average. I also need to know what I have to maintain to get the average I
am looking for. Example: I need to average .075 over a 24 hour period, lets
say that I have 12 hours of data, i need to know what I have to average the
remaining hours to get my .075. I enter the hourly numbers myself.

#2
May 12th 09, 05:57 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 694
hourly average help

Bob:

There is one function AVERAGE(B2:B13) and this will give the average of the
numbers.

The other method is to use SUM(B2:B13)/12 assuming the data is in B2:B13
and the time in A2:A13.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK

"bob" wrote:

I need help in trying to set up a work sheet that will keep track of an
average. I also need to know what I have to maintain to get the average I
am looking for. Example: I need to average .075 over a 24 hour period, lets
say that I have 12 hours of data, i need to know what I have to average the
remaining hours to get my .075. I enter the hourly numbers myself.

#3
May 12th 09, 08:48 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 2,480
hourly average help

Hi Bob

Assuming your data is entered in A1:X1
=(24*0.075-SUM(A1:INDEX(A1:X1,COUNT(A1:X1))))/
COUNTBLANK(A1:X1)
will give you the average required for the remaining unfilled hours
--
Regards
Roger Govier

"bob" wrote in message
...
I need help in trying to set up a work sheet that will keep track of an
average. I also need to know what I have to maintain to get the average I
am looking for. Example: I need to average .075 over a 24 hour period,
lets
say that I have 12 hours of data, i need to know what I have to average
the
remaining hours to get my .075. I enter the hourly numbers myself.

#4
May 12th 09, 08:55 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 2,059
hourly average help

This seems like an algebra question, not an Excel question. (Although there
could be an Excel question behind it.)

In general, if you have n of 24 hourly data, h[1] through h[n], then you are
looking for x, the average remaining hourly data, such that:

( h[1] + ... + h[n] + (24-n)*x ) / 24 = 0.075

So:

x = ( 24*0.075 - (h[1] + ... + h[n]) ) / (24-n)

In Excel terms, if A1 contains the target average (0.075) and if B1 up to
B24 contain the hourly data that you have, then:

=(24*A1 - SUM(B1:B24)) / (24 - COUNT(B1:B24))

----- original message -----

"bob" wrote:
I need help in trying to set up a work sheet that will keep track of an
average. I also need to know what I have to maintain to get the average I
am looking for. Example: I need to average .075 over a 24 hour period,
lets
say that I have 12 hours of data, i need to know what I have to average
the
remaining hours to get my .075. I enter the hourly numbers myself.

#5
May 13th 09, 04:09 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: May 2009 Posts: 8
hourly average help

Thanks for the help. I will try and clarify.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

Assuming your data is entered in A1:X1
=(24*0.075-SUM(A1:INDEX(A1:X1,COUNT(A1:X1))))/
COUNTBLANK(A1:X1)
will give you the average required for the remaining unfilled hours
--
Regards
Roger Govier

"bob" wrote in message
...
I need help in trying to set up a work sheet that will keep track of an
average. I also need to know what I have to maintain to get the average
I
am looking for. Example: I need to average .075 over a 24 hour period,
lets
say that I have 12 hours of data, i need to know what I have to average
the
remaining hours to get my .075. I enter the hourly numbers myself.

#6
May 13th 09, 10:27 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 2,480
hourly average help

Hi Bob

In cell C24 enter
=(24*0.075-SUM(A1:INDEX(A1:A24,COUNT(A1:A24))))
/COUNTBLANK(A1:A24)

--
Regards
Roger Govier

"bob" wrote in message
...
Thanks for the help. I will try and clarify.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

Assuming your data is entered in A1:X1
=(24*0.075-SUM(A1:INDEX(A1:X1,COUNT(A1:X1))))/
COUNTBLANK(A1:X1)
will give you the average required for the remaining unfilled hours
--
Regards
Roger Govier

"bob" wrote in message
...
I need help in trying to set up a work sheet that will keep track of an
average. I also need to know what I have to maintain to get the average
I
am looking for. Example: I need to average .075 over a 24 hour period,
lets
say that I have 12 hours of data, i need to know what I have to average
the
remaining hours to get my .075. I enter the hourly numbers myself.

#7
June 25th 09, 06:49 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: May 2009 Posts: 8
hourly average help

Thanks for all the help but none of the formulas are giving me what I want.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

In cell C24 enter
=(24*0.075-SUM(A1:INDEX(A1:A24,COUNT(A1:A24))))
/COUNTBLANK(A1:A24)

--
Regards
Roger Govier

"bob" wrote in message
...
Thanks for the help. I will try and clarify.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

Assuming your data is entered in A1:X1
=(24*0.075-SUM(A1:INDEX(A1:X1,COUNT(A1:X1))))/
COUNTBLANK(A1:X1)
will give you the average required for the remaining unfilled hours
--
Regards
Roger Govier

"bob" wrote in message
...
I need help in trying to set up a work sheet that will keep track of an
average. I also need to know what I have to maintain to get the
average I
am looking for. Example: I need to average .075 over a 24 hour period,
lets
say that I have 12 hours of data, i need to know what I have to average
the
remaining hours to get my .075. I enter the hourly numbers myself.

#8
June 28th 09, 05:55 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 2,480
hourly average help

Hi Bob

Explain what you have done, what is not right and what it is that you want,
then maybe we can give some further help.

--
Regards
Roger Govier

"bob" wrote in message
...
Thanks for all the help but none of the formulas are giving me what I
want.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

In cell C24 enter
=(24*0.075-SUM(A1:INDEX(A1:A24,COUNT(A1:A24))))
/COUNTBLANK(A1:A24)

--
Regards
Roger Govier

"bob" wrote in message
...
Thanks for the help. I will try and clarify.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

Assuming your data is entered in A1:X1
=(24*0.075-SUM(A1:INDEX(A1:X1,COUNT(A1:X1))))/
COUNTBLANK(A1:X1)
will give you the average required for the remaining unfilled hours
--
Regards
Roger Govier

"bob" wrote in message
...
I need help in trying to set up a work sheet that will keep track of
an
average. I also need to know what I have to maintain to get the
average I
am looking for. Example: I need to average .075 over a 24 hour
period,
lets
say that I have 12 hours of data, i need to know what I have to
average
the
remaining hours to get my .075. I enter the hourly numbers myself.

#9
July 4th 09, 12:49 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: May 2009 Posts: 8
hourly average help

I will try and explain. I have a number that I need to meet for a 24hr
average. That is .075. Lets say that I put all the data in columns A1 :
A24 as each hour completes. In another cell, lets say C24 I want it to
say what I need to average in the the unfilled cells so that I get my
average of .075. I do not know how to explain it any better. I had a
spreadsheet that use to work but I cannot seem to find it.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

Explain what you have done, what is not right and what it is that you
want, then maybe we can give some further help.

--
Regards
Roger Govier

"bob" wrote in message
...
Thanks for all the help but none of the formulas are giving me what I
want.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

In cell C24 enter
=(24*0.075-SUM(A1:INDEX(A1:A24,COUNT(A1:A24))))
/COUNTBLANK(A1:A24)

--
Regards
Roger Govier

"bob" wrote in message
...
Thanks for the help. I will try and clarify.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

Assuming your data is entered in A1:X1
=(24*0.075-SUM(A1:INDEX(A1:X1,COUNT(A1:X1))))/
COUNTBLANK(A1:X1)
will give you the average required for the remaining unfilled hours
--
Regards
Roger Govier

"bob" wrote in message
...
I need help in trying to set up a work sheet that will keep track of
an
average. I also need to know what I have to maintain to get the
average I
am looking for. Example: I need to average .075 over a 24 hour
period,
lets
say that I have 12 hours of data, i need to know what I have to
average
the
remaining hours to get my .075. I enter the hourly numbers myself.

#10
July 4th 09, 09:36 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 2,480
hourly average help

Hi Bob

Well that is exactly what the last formula I supplied does.

If you want to send me a copy of your workbook, I will take a look for you.
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

--
Regards
Roger Govier

"bob" wrote in message
...
I will try and explain. I have a number that I need to meet for a 24hr
average. That is .075. Lets say that I put all the data in columns A1 :
A24 as each hour completes. In another cell, lets say C24 I want it to
say what I need to average in the the unfilled cells so that I get my
average of .075. I do not know how to explain it any better. I had a
spreadsheet that use to work but I cannot seem to find it.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

Explain what you have done, what is not right and what it is that you
want, then maybe we can give some further help.

--
Regards
Roger Govier

"bob" wrote in message
...
Thanks for all the help but none of the formulas are giving me what I
want.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

In cell C24 enter
=(24*0.075-SUM(A1:INDEX(A1:A24,COUNT(A1:A24))))
/COUNTBLANK(A1:A24)

--
Regards
Roger Govier

"bob" wrote in message
...
Thanks for the help. I will try and clarify.
"Roger Govier" <[email protected] wrote in message
...
Hi Bob

Assuming your data is entered in A1:X1
=(24*0.075-SUM(A1:INDEX(A1:X1,COUNT(A1:X1))))/
COUNTBLANK(A1:X1)
will give you the average required for the remaining unfilled hours
--
Regards
Roger Govier

"bob" wrote in message
...
I need help in trying to set up a work sheet that will keep track of
an
average. I also need to know what I have to maintain to get the
average I
am looking for. Example: I need to average .075 over a 24 hour
period,
lets
say that I have 12 hours of data, i need to know what I have to
average
the
remaining hours to get my .075. I enter the hourly numbers myself.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Lindsey Excel Discussion (Misc queries) 1 March 30th 09 05:05 PM Chuck Walsh Excel Discussion (Misc queries) 1 October 23rd 07 09:07 PM kippers Excel Discussion (Misc queries) 5 February 1st 07 10:27 AM Monty Excel Discussion (Misc queries) 6 March 24th 06 08:53 AM Dreamstar_1961 Excel Worksheet Functions 3 November 2nd 05 11:33 AM

All times are GMT +1. The time now is 09:17 PM.