ExcelBanter - average

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - average (https://www.excelbanter.com/excel-discussion-misc-queries/229132-average.html)

dernspiker

average

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

average

Try

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

If this post helps click Yes
---------------
Jacob Skaria

"dernspiker" wrote:

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

T. Valko

average

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

--
Biff
Microsoft Excel MVP

"dernspiker" wrote in message
...
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

dernspiker

average

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

"Jacob Skaria" wrote:

Try

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

If this post helps click Yes
---------------
Jacob Skaria

"dernspiker" wrote:

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

T. Valko

average

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

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
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),""))

--
Biff
Microsoft Excel MVP

"dernspiker" wrote in message
...
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

dernspiker

average

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

THANKS!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"dernspiker" wrote in message
...
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

dernspiker

average

I Caught that, and fixed it already...

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
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),""))

--
Biff
Microsoft Excel MVP

"dernspiker" wrote in message
...
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

T. Valko

average

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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

THANKS!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"dernspiker" wrote in message
...
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

T. Valko

average

Good deal!

--
Biff
Microsoft Excel MVP

"dernspiker" wrote in message
...
I Caught that, and fixed it already...

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
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),""))

--
Biff
Microsoft Excel MVP

"dernspiker" wrote in message
...
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

All times are GMT +1. The time now is 12:25 AM.