average function

T

treborl

I am trying to find an average function. I have a series of numbers,
24 to be exact. Each number is an hourly average. Lets say cell A1
to A25. There is also a daily average in cell 26. I need a formula
that will tell me what I have to average in the open cells to get my
daily average of 144.2. Cells are only filled as the hour completes.
 
D

Dave Peterson

Maybe...

=IF(COUNT($A$1:$A$25)=25,"Out of input cells!",
(($A$26*25)-SUM($A$1:$A$25))/(25-COUNT($A$1:$A$25)))
 
T

treborl

Maybe...

=IF(COUNT($A$1:$A$25)=25,"Out of input cells!",
  (($A$26*25)-SUM($A$1:$A$25))/(25-COUNT($A$1:$A$25)))
Click to expand...

That gives me the same results as =average(A1:A24) which is the
current average.
 
T

treborl

What numbers do you have in A1:A24?







--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

A1 :A24 have the hourly averages for the day. But they only have
numbers as the hours pass. Lets say that 16 cells have numbers in
them. I need to know what numbers I need in the remaining cells to
not go over a 144.2 average for the day. Not sure if this is
possible. What I do to make sure I do not go over the average is just
put a random number in the remaining cells and see what that gives me
for an average.
 
S

Sandy Mann

treborl,

For 24 numbers to have an average of 114.2 the total of all the numbers must
be 114.2*24 = 2740.8

in C2 enter the formula:

=IF(COUNT(A1:A24)=2,(114.2*24-SUM($A$1:A1))/(ROW($A$25)-ROW()),"")

and drag down to C24 then hide Column C

In B2 enter the formula:

=IF(A2="",LOOKUP(10^10,$C$2:$C$24),"")

and drag down to B24. The required *missing* numbers will now show in
Column B so the average of A1:B24 will be 114.2


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk


What numbers do you have in A1:A24?







--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

Go here for the file. http://www.savefile.com/projects/808541825
 
D

Dave Peterson

I don't open unknown files.

Why not just include your values as a column of text?

And include what you think the results should be.
 
T

treborl

treborl,

For 24 numbers to have an average of 114.2 the total of all the numbers must
be 114.2*24 = 2740.8

in C2 enter the formula:

=IF(COUNT(A1:A24)=2,(114.2*24-SUM($A$1:A1))/(ROW($A$25)-ROW()),"")

and drag down to C24 then hide Column C

In B2 enter the formula:

=IF(A2="",LOOKUP(10^10,$C$2:$C$24),"")

and drag down to B24.  The required *missing* numbers will now show in
Column B so the average of A1:B24 will be 114.2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk






Go here for the file.  http://www.savefile.com/projects/808541825- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Sandy. That works out great. Just what I was looking for.
 
S

Sandy Mann

Glad that it worked for you, thanks for the feedback

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk


treborl,

For 24 numbers to have an average of 114.2 the total of all the numbers
must
be 114.2*24 = 2740.8

in C2 enter the formula:

=IF(COUNT(A1:A24)=2,(114.2*24-SUM($A$1:A1))/(ROW($A$25)-ROW()),"")

and drag down to C24 then hide Column C

In B2 enter the formula:

=IF(A2="",LOOKUP(10^10,$C$2:$C$24),"")

and drag down to B24. The required *missing* numbers will now show in
Column B so the average of A1:B24 will be 114.2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk






Go here for the file. http://www.savefile.com/projects/808541825- Hide
quoted text -

- Show quoted text -
Click to expand...

Thanks Sandy. That works out great. Just what I was looking for.
 
Top