Hi All,

I have a spreadsheet with an Array entered Formula (based on Rows using the
TRANSPOSE Function) that produces the #N/A error when no further data is
available to be calculated: this in itself is fine - the #N/A is disguised
with Conditional Formatting. However, if possible, I would like to find the
AVERAGE of the values in each Row excluding those cells with #N/A from the
calculation.

I've tried a few variations using either the ISERROR or ISNA Function nested
with the AVERAGE Function. I get either zero or #N/A returned.

The original Formulae providing the results that I now wish to Average was
brilliantly created by Dominic in this previous Post: "Count Intervals of
Filtered TEXT values in Column and Return Count across a Row"

http://www.officekb.com/Uwe/Forum.as...40OfficeKB.com


The values returned from the above Formulae is numeric and formatted as
GENERAL.

Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

try this array formula entered with ctrl+shift+enter
=AVERAGE(IF(NOT(ISERROR($C$8:$C$10)),$C$8:$C$10))

--
Don Guillett
SalesAid Software

"Sam via OfficeKB.com" wrote in message
...

Hi All,

I have a spreadsheet with an Array entered Formula (based on Rows using
the
TRANSPOSE Function) that produces the #N/A error when no further data is
available to be calculated: this in itself is fine - the #N/A is disguised
with Conditional Formatting. However, if possible, I would like to find
the
AVERAGE of the values in each Row excluding those cells with #N/A from the
calculation.

I've tried a few variations using either the ISERROR or ISNA Function
nested
with the AVERAGE Function. I get either zero or #N/A returned.

The original Formulae providing the results that I now wish to Average was
brilliantly created by Dominic in this previous Post: "Count Intervals of
Filtered TEXT values in Column and Return Count across a Row"


http://www.officekb.com/Uwe/Forum.as...40OfficeKB.com


The values returned from the above Formulae is numeric and formatted as
GENERAL.

Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

Hi Don,

Thank you very much for your assistance - your Formula did the job.

Cheers
Sam

Don Guillett wrote:
try this array formula entered with ctrl+shift+enter
=AVERAGE(IF(NOT(ISERROR($C$8:$C$10)),$C$8:$C$10 ))


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

Hi!

brilliantly created by Dominic

He has a habit of doing that!

=SUMIF(A1:E1,"<#N/A")/COUNTIF(A1:E1,"0")

Biff

"Sam via OfficeKB.com" wrote in message
...

Hi All,

I have a spreadsheet with an Array entered Formula (based on Rows using
the
TRANSPOSE Function) that produces the #N/A error when no further data is
available to be calculated: this in itself is fine - the #N/A is disguised
with Conditional Formatting. However, if possible, I would like to find
the
AVERAGE of the values in each Row excluding those cells with #N/A from the
calculation.

I've tried a few variations using either the ISERROR or ISNA Function
nested
with the AVERAGE Function. I get either zero or #N/A returned.

The original Formulae providing the results that I now wish to Average was
brilliantly created by Dominic in this previous Post: "Count Intervals of
Filtered TEXT values in Column and Return Count across a Row"

http://www.officekb.com/Uwe/Forum.as...40OfficeKB.com


The values returned from the above Formulae is numeric and formatted as
GENERAL.

Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

brilliantly created by Dominic
Believe there's a typo there ... should be: Domenic

He has a habit of doing that!
I'm guessing <g it should read as: She has a habit of doing that!

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max!

He has a habit of doing that!
I'm guessing <g it should read as: She has a habit of doing that!

My apologies if Domenic is not a "he" !

Domenic, sounds like a "he" to me! BWDIK!

Biff

"Max" wrote in message
...
brilliantly created by Dominic
Believe there's a typo there ... should be: Domenic

He has a habit of doing that!
I'm guessing <g it should read as: She has a habit of doing that!

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

He has a habit of doing that!
... and perhaps Domenic could put to rest our guesses <g !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

In article ,
"Max" wrote:

.. and perhaps Domenic could put to rest our guesses <g !

I just checked and it seems that I'm a 'he'. <VBG

So it looks like Biff get's the C-gar! <BG

Hi Max,

I thought I'd do the honourable thing and own up to my typo that started this
conversation. It is Domenic, as you said.

Cheers
Sam

Max wrote:
brilliantly created by Dominic
Believe there's a typo there ... should be: Domenic

Biff wrote:
He has a habit of doing that!

Max wrote:
I'm guessing <g it should read as: She has a habit of doing that!



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

... It is Domenic, as you said.

well <g .. I did trace the link you posted to double-check ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Biff,

Thank you very much - your Formula works well too.

Thanks
Sam

Biff wrote:
=SUMIF(A1:E1,"<#N/A")/COUNTIF(A1:E1,"0")


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

An alternative to the solutions provided by Biff and Don would be to
change the formula to return a blank instead of #N/A...

1) Select NewSheet!B8:L8

2) Enter the following formula:

=IF(COLUMN()-COLUMN(B8)+1<COUNTIF(Range,A8)+(FirstRow=""),TRANS POSE(Array
2-Array1)-1,"")

3) Confirm with CONTROL+SHIFT+ENTER

In this case, there would be no need to use conditional formatting.

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi All,

I have a spreadsheet with an Array entered Formula (based on Rows using the
TRANSPOSE Function) that produces the #N/A error when no further data is
available to be calculated: this in itself is fine - the #N/A is disguised
with Conditional Formatting. However, if possible, I would like to find the
AVERAGE of the values in each Row excluding those cells with #N/A from the
calculation.

I've tried a few variations using either the ISERROR or ISNA Function nested
with the AVERAGE Function. I get either zero or #N/A returned.

The original Formulae providing the results that I now wish to Average was
brilliantly created by Dominic in this previous Post: "Count Intervals of
Filtered TEXT values in Column and Return Count across a Row"

http://www.officekb.com/Uwe/Forum.as...nt-Intervals-o
f-Filtered-TEXT-values-in-Column-and#51E8B7DD4D490%40OfficeKB.com


The values returned from the above Formulae is numeric and formatted as
GENERAL.

Thanks
Sam

Hi Domenic,

Thank you very much for additional assistance and great working Formulas.

Cheers
Sam

Domenic wrote:
An alternative to the solutions provided by Biff and Don would be to
change the formula to return a blank instead of #N/A...

1) Select NewSheet!B8:L8
2) Enter the following formula:
=IF(COLUMN()-COLUMN(B8)+1<COUNTIF(Range,A8)+(FirstRow=""),TRANS POSE(Array
2-Array1)-1,"")
3) Confirm with CONTROL+SHIFT+ENTER
In this case, there would be no need to use conditional formatting.

Hope this helps!


--
Message posted via http://www.officekb.com