Stopping an Entry

[Ed O'Brien]

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

 

[cm]

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

 

[Eduardo]

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)

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[Ed O'Brien]

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

 

[David Biddulph]

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

 

[Gord Dibben]

Thanks David


Gord

 

[Ed O'Brien]

Thank you, David.

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

This works fine. Thanks a million.

Ed

 

[David Biddulph]

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.

 

[Gord Dibben]

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


Gord

 

[David Biddulph]

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

 

[Gord Dibben]

No reason for a TRUE that I can see.


Gord

 

[Ed O'Brien]

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