average

[dernspiker]

In Cell N4 I need to average:

D4 G4 J4 M4

They may or may not cotain N/A

If they ALL contain N/A I need it to return and N/A

I will have more questions later today.

Thank you,
Dana

 

[Jacob Skaria]

Try

=SUMPRODUCT(AVERAGE(INDIRECT({"D4","G4","J4","M4"})))

If this post helps click Yes

 

[T. Valko]

They may or may not cotain N/A

Is that the TEXT string N/A or the Excel error value #N/A?

If it's the text string N/A try this:

=IF(D4&G4&J4&M4="NANANANA","NA",IF(COUNT(D4,G4,J4,M4),AVERAGE(D4,G4,J4,M4),""))

 

[dernspiker]

This gives me a #div/0! reply....

Try

=SUMPRODUCT(AVERAGE(INDIRECT({"D4","G4","J4","M4"})))

If this post helps click Yes

 

[T. Valko]

=IF(D4&G4&J4&M4="NANANANA"

Ooops!

I forgot to include the slash in the N/A string:

Change to:

=IF(D4&G4&J4&M4="N/AN/AN/AN/A"

Or:

=IF(D4&G4&J4&M4=REPT("N/A",4)

 

[dernspiker]

Oh my GOSH!!! Thats it!!! Thank you sooooo much... I have been trying to
find this formula for quite a while.

THANKS!

 

[dernspiker]

I Caught that, and fixed it already...

Ooops!

I forgot to include the slash in the N/A string:

Change to:

=IF(D4&G4&J4&M4="N/AN/AN/AN/A"

Or:

=IF(D4&G4&J4&M4=REPT("N/A",4)

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[T. Valko]

Good deal!

--
Biff
Microsoft Excel MVP

I Caught that, and fixed it already...