I have a cell (AW4) into which one types a figure representing a previous
period's accumulated average. (The sheet is cleared after each period and
restarted)

I have another cell (AU4) which provides a current average across numerous
rows. (There are about 40 rows)

A third cell (AU1) shows a perpetual average from these two cells.

This formula, in cell AU1, is simple enough - =AVERAGE(AU4,AW4).

However, there are times when the user will not enter a figure in cell AW4.
When this happens the average shown in cell AU1 is only current and not a
perpetual reading, which can be misleading, so in this event, I want AU1 to
remain blank. In other words. if nothing is entered in cell AW4 how do I
adjust the formula to prevent a figure (from only AU4) appearing in cell
AU1?

TIA for any help.

Ed

=if(AW4="","",AVERAGE(AU4,AW4))


"Ed O'Brien" wrote:

I have a cell (AW4) into which one types a figure representing a previous
period's accumulated average. (The sheet is cleared after each period and
restarted)

I have another cell (AU4) which provides a current average across numerous
rows. (There are about 40 rows)

A third cell (AU1) shows a perpetual average from these two cells.

This formula, in cell AU1, is simple enough - =AVERAGE(AU4,AW4).

However, there are times when the user will not enter a figure in cell AW4.
When this happens the average shown in cell AU1 is only current and not a
perpetual reading, which can be misleading, so in this event, I want AU1 to
remain blank. In other words. if nothing is entered in cell AW4 how do I
adjust the formula to prevent a figure (from only AU4) appearing in cell
AU1?

TIA for any help.

Ed

Hi,
not sure if I understood what you want, if you want the formula to show
blank if AW4 is empty use

=if(AW4="","",=AVERAGE(AU4,AW4)

if AW4 is empty you want the value from AU4, use

=if(AW4="",AU4,=AVERAGE(AU4,AW4)

"Ed O'Brien" wrote:

I have a cell (AW4) into which one types a figure representing a previous
period's accumulated average. (The sheet is cleared after each period and
restarted)

I have another cell (AU4) which provides a current average across numerous
rows. (There are about 40 rows)

A third cell (AU1) shows a perpetual average from these two cells.

This formula, in cell AU1, is simple enough - =AVERAGE(AU4,AW4).

However, there are times when the user will not enter a figure in cell AW4.
When this happens the average shown in cell AU1 is only current and not a
perpetual reading, which can be misleading, so in this event, I want AU1 to
remain blank. In other words. if nothing is entered in cell AW4 how do I
adjust the formula to prevent a figure (from only AU4) appearing in cell
AU1?

TIA for any help.

Ed

=IF(AW4="","",Average(AU4,AW4)


Gord Dibben MS Excel MVP

On Thu, 1 Oct 2009 16:42:55 +0100, "Ed O'Brien"
wrote:

I have a cell (AW4) into which one types a figure representing a previous
period's accumulated average. (The sheet is cleared after each period and
restarted)

I have another cell (AU4) which provides a current average across numerous
rows. (There are about 40 rows)

A third cell (AU1) shows a perpetual average from these two cells.

This formula, in cell AU1, is simple enough - =AVERAGE(AU4,AW4).

However, there are times when the user will not enter a figure in cell AW4.
When this happens the average shown in cell AU1 is only current and not a
perpetual reading, which can be misleading, so in this event, I want AU1 to
remain blank. In other words. if nothing is entered in cell AW4 how do I
adjust the formula to prevent a figure (from only AU4) appearing in cell
AU1?

TIA for any help.

Ed

Thanks, Gord, and everyone else.

None appear to work completely... Probably something I've done.

I used your formula, Gord but all I get is the word "True" when AW4 has a
figure inserted.

Most peculiar...

Any ideas?

Ed



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
=IF(AW4="","",Average(AU4,AW4)


Gord Dibben MS Excel MVP

On Thu, 1 Oct 2009 16:42:55 +0100, "Ed O'Brien"
wrote:

I have a cell (AW4) into which one types a figure representing a previous
period's accumulated average. (The sheet is cleared after each period and
restarted)

I have another cell (AU4) which provides a current average across numerous
rows. (There are about 40 rows)

A third cell (AU1) shows a perpetual average from these two cells.

This formula, in cell AU1, is simple enough - =AVERAGE(AU4,AW4).

However, there are times when the user will not enter a figure in cell
AW4.
When this happens the average shown in cell AU1 is only current and not a
perpetual reading, which can be misleading, so in this event, I want AU1
to
remain blank. In other words. if nothing is entered in cell AW4 how do I
adjust the formula to prevent a figure (from only AU4) appearing in cell
AU1?

TIA for any help.

Ed

Gord's formula is missing a parenthesis, so you presumably didn't use his
formula? I don't know what modification you did to his formula to get the
result showing TRUE, but what I'm sure he intended to say was
=IF(AW4="","",AVERAGE(AU4,AW4))
--
David Biddulph

"Ed O'Brien" wrote in message
...
Thanks, Gord, and everyone else.

None appear to work completely... Probably something I've done.

I used your formula, Gord but all I get is the word "True" when AW4 has a
figure inserted.

Most peculiar...

Any ideas?

Ed



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
=IF(AW4="","",Average(AU4,AW4)


Gord Dibben MS Excel MVP

On Thu, 1 Oct 2009 16:42:55 +0100, "Ed O'Brien"
wrote:

I have a cell (AW4) into which one types a figure representing a previous
period's accumulated average. (The sheet is cleared after each period and
restarted)

I have another cell (AU4) which provides a current average across
numerous
rows. (There are about 40 rows)

A third cell (AU1) shows a perpetual average from these two cells.

This formula, in cell AU1, is simple enough - =AVERAGE(AU4,AW4).

However, there are times when the user will not enter a figure in cell
AW4.
When this happens the average shown in cell AU1 is only current and not a
perpetual reading, which can be misleading, so in this event, I want AU1
to
remain blank. In other words. if nothing is entered in cell AW4 how do I
adjust the formula to prevent a figure (from only AU4) appearing in cell
AU1?

TIA for any help.

Ed

Thanks David


Gord

On Thu, 1 Oct 2009 18:54:23 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Gord's formula is missing a parenthesis, so you presumably didn't use his
formula? I don't know what modification you did to his formula to get the
result showing TRUE, but what I'm sure he intended to say was
=IF(AW4="","",AVERAGE(AU4,AW4))

Thank you, David.

I don't know either. I merely copied and pasted the formula.

This works fine. Thanks a million.

Ed



"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Gord's formula is missing a parenthesis, so you presumably didn't use his
formula? I don't know what modification you did to his formula to get the
result showing TRUE, but what I'm sure he intended to say was
=IF(AW4="","",AVERAGE(AU4,AW4))
--
David Biddulph

"Ed O'Brien" wrote in message
...
Thanks, Gord, and everyone else.

None appear to work completely... Probably something I've done.

I used your formula, Gord but all I get is the word "True" when AW4 has a
figure inserted.

Most peculiar...

Any ideas?

Ed



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
=IF(AW4="","",Average(AU4,AW4)


Gord Dibben MS Excel MVP

On Thu, 1 Oct 2009 16:42:55 +0100, "Ed O'Brien"
wrote:

I have a cell (AW4) into which one types a figure representing a
previous
period's accumulated average. (The sheet is cleared after each period
and
restarted)

I have another cell (AU4) which provides a current average across
numerous
rows. (There are about 40 rows)

A third cell (AU1) shows a perpetual average from these two cells.

This formula, in cell AU1, is simple enough - =AVERAGE(AU4,AW4).

However, there are times when the user will not enter a figure in cell
AW4.
When this happens the average shown in cell AU1 is only current and not
a
perpetual reading, which can be misleading, so in this event, I want
AU1 to
remain blank. In other words. if nothing is entered in cell AW4 how do I
adjust the formula to prevent a figure (from only AU4) appearing in cell
AU1?

TIA for any help.

Ed

If you merely copied and pasted Gord's formula, Excel would have said that
it was invalid and refused to accept it until the formula was changed.
--
David Biddulph

"Ed O'Brien" wrote in message
...
Thank you, David.

I don't know either. I merely copied and pasted the formula.

This works fine. Thanks a million.

Ed



"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Gord's formula is missing a parenthesis, so you presumably didn't use his
formula? I don't know what modification you did to his formula to get
the result showing TRUE, but what I'm sure he intended to say was
=IF(AW4="","",AVERAGE(AU4,AW4))
--
David Biddulph

"Ed O'Brien" wrote in message
...
Thanks, Gord, and everyone else.

None appear to work completely... Probably something I've done.

I used your formula, Gord but all I get is the word "True" when AW4 has
a figure inserted.

Most peculiar...

Any ideas?

Ed



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
=IF(AW4="","",Average(AU4,AW4)


Gord Dibben MS Excel MVP

On Thu, 1 Oct 2009 16:42:55 +0100, "Ed O'Brien"
wrote:

I have a cell (AW4) into which one types a figure representing a
previous
period's accumulated average. (The sheet is cleared after each period
and
restarted)

I have another cell (AU4) which provides a current average across
numerous
rows. (There are about 40 rows)

A third cell (AU1) shows a perpetual average from these two cells.

This formula, in cell AU1, is simple enough - =AVERAGE(AU4,AW4).

However, there are times when the user will not enter a figure in cell
AW4.
When this happens the average shown in cell AU1 is only current and not
a
perpetual reading, which can be misleading, so in this event, I want
AU1 to
remain blank. In other words. if nothing is entered in cell AW4 how do
I
adjust the formula to prevent a figure (from only AU4) appearing in
cell
AU1?

TIA for any help.

Ed

Actually when I paste the formula into a cell Excel suggests a correction by
adding the missing parens.


Gord

On Thu, 1 Oct 2009 20:40:32 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

If you merely copied and pasted Gord's formula, Excel would have said that
it was invalid and refused to accept it until the formula was changed.

Yes, but with that correction I don't believe that the formula can return a
result of TRUE, can it, Gord?
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Actually when I paste the formula into a cell Excel suggests a correction
by
adding the missing parens.


Gord

On Thu, 1 Oct 2009 20:40:32 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

If you merely copied and pasted Gord's formula, Excel would have said that
it was invalid and refused to accept it until the formula was changed.

No reason for a TRUE that I can see.


Gord



On Thu, 1 Oct 2009 21:39:59 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Yes, but with that correction I don't believe that the formula can return a
result of TRUE, can it, Gord?

Well, Gentlemen, I have tried to recreate the situation but can't. I
definitely got "True" after copying and pasting but I guess when deleting
something must have got left behind.

Sorry for all th fuss.

Ed


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
No reason for a TRUE that I can see.


Gord



On Thu, 1 Oct 2009 21:39:59 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Yes, but with that correction I don't believe that the formula can return
a
result of TRUE, can it, Gord?