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. |
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. |
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. |
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. |
hourly average help
Thanks for the help. I will try and clarify.
"Roger Govier" <roger@technology4unospamdotcodotuk 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. |
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" <roger@technology4unospamdotcodotuk 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. |
hourly average help
Thanks for all the help but none of the formulas are giving me what I want.
"Roger Govier" <roger@technology4unospamdotcodotuk 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" <roger@technology4unospamdotcodotuk 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. |
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" <roger@technology4unospamdotcodotuk 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" <roger@technology4unospamdotcodotuk 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. |
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" <roger@technology4unospamdotcodotuk 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" <roger@technology4unospamdotcodotuk 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" <roger@technology4unospamdotcodotuk 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. |
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" <roger@technology4unospamdotcodotuk 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" <roger@technology4unospamdotcodotuk 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" <roger@technology4unospamdotcodotuk 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. |
hourly average help
Sorry Roger I must have copied it wrong. I tried it again and it works
fine. Thanks "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... 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" <roger@technology4unospamdotcodotuk 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" <roger@technology4unospamdotcodotuk 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" <roger@technology4unospamdotcodotuk 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. |
All times are GMT +1. The time now is 02:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com