average function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

treborl - 29 Jan 2008 23:31 GMT

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.

Reply to this Message

Dave Peterson - 30 Jan 2008 00:05 GMT

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

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

Signature

Dave Peterson

Reply to this Message

treborl - 30 Jan 2008 00:46 GMT

> Maybe...
>
[quoted text clipped - 10 lines]
>
> Dave Peterson

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

Reply to this Message

Dave Peterson - 30 Jan 2008 01:02 GMT

What numbers do you have in A1:A24?

> > Maybe...
> >
[quoted text clipped - 13 lines]
> That gives me the same results as =average(A1:A24) which is the
> current average.

Signature

Dave Peterson

Reply to this Message

treborl - 30 Jan 2008 03:18 GMT

> What numbers do you have in A1:A24?
>
[quoted text clipped - 21 lines]
>
> - Show quoted text -

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.

Reply to this Message

treborl - 30 Jan 2008 03:32 GMT

> What numbers do you have in A1:A24?
>
[quoted text clipped - 21 lines]
>
> - Show quoted text -

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

Reply to this Message

Sandy Mann - 30 Jan 2008 11:14 GMT

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

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

On Jan 29, 8:02 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:

> What numbers do you have in A1:A24?
>
[quoted text clipped - 26 lines]
>
> - Show quoted text -

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

Reply to this Message

treborl - 01 Feb 2008 13:59 GMT

> treborl,
>
[quoted text clipped - 60 lines]
>
> - Show quoted text -

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

Reply to this Message

Sandy Mann - 01 Feb 2008 14:14 GMT

Glad that it worked for you, thanks for the feedback

Signature

Regards,

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

On Jan 30, 6:14 am, "Sandy Mann" <sandyma...@mailinator.com> wrote:

> treborl,
>
[quoted text clipped - 63 lines]
>
> - Show quoted text -

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

Reply to this Message

Dave Peterson - 30 Jan 2008 11:31 GMT

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.

<<snipped>>

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

Signature

Dave Peterson

Reply to this Message