hourly average help

[bob]

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.

 

[Martin Fishlock]

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.

 

[Roger Govier]

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

 

[JoeU2004]

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 -----

 

[Roger Govier]

Hi Bob

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

 

[bob]

Thanks for all the help but none of the formulas are giving me what I want.

 

[Roger Govier]

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.

 

[bob]

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]

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

 

[bob]

Sorry Roger I must have copied it wrong. I tried it again and it works
fine. Thanks