Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 12th 09, 02:32 AM posted to microsoft.public.excel.newusers
bob bob is offline
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 8
Default 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   Report Post  
Old May 12th 09, 05:57 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 694
Default 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
Please do not forget to rate this reply.


"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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old May 13th 09, 04:09 PM posted to microsoft.public.excel.newusers
bob bob is offline
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 8
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old June 25th 09, 06:49 AM posted to microsoft.public.excel.newusers
bob bob is offline
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 8
Default 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   Report Post  
Old 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
Default hourly average help

Hi Bob

That's not too helpful.
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   Report Post  
Old July 4th 09, 12:49 AM posted to microsoft.public.excel.newusers
bob bob is offline
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 8
Default 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

That's not too helpful.
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   Report Post  
Old 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
Default 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

That's not too helpful.
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.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I average hourly data into daily data? Lindsey Excel Discussion (Misc queries) 1 March 30th 09 05:05 PM
Using a time value in an Excel formula to figure an hourly average Chuck Walsh Excel Discussion (Misc queries) 1 October 23rd 07 09:07 PM
How to convert half hourly data into hourly kippers Excel Discussion (Misc queries) 5 February 1st 07 10:27 AM
hourly rates Monty Excel Discussion (Misc queries) 6 March 24th 06 08:53 AM
Hourly rates Dreamstar_1961 Excel Worksheet Functions 3 November 2nd 05 11:33 AM


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

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017