average function

  • Thread starter Thread starter treborl
  • Start date Start date
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.
 
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)))
 
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.
 
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.
 
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

(e-mail address removed)
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
 
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.
 
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

(e-mail address removed)
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.
 
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

(e-mail address removed)
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

(e-mail address removed)
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.
 
Back
Top