Error Handling #N/A with AVERAGE Function - Average of values in Row

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

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=""),TRANSPOSE(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

Thanks, Domenic !
Another intuitive guess bites the dust .. <bg>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

In article <[email protected]>,
"Max" <[email protected]> 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>

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=""),TRANSPOSE(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 <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> 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

> ... 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 <at>yahoo<dot>com
----

> >> 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 <at>yahoo<dot>com
----

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

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

> >> 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 <at>yahoo<dot>com
----

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
[email protected]
"Sam via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> 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!

>> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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 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" <[email protected]> wrote in message
news:[email protected]...
>> >> 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 <at>yahoo<dot>com
> ----
>
>

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

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

> >> 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 <at>yahoo<dot>com
----

> >> 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 <at>yahoo<dot>com
----

> ... 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 <at>yahoo<dot>com
----

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

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=""),TRANSPOSE(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 <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> 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!

>> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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 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" <[email protected]> wrote in message
news:[email protected]...
>> >> 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 <at>yahoo<dot>com
> ----
>
>

In article <[email protected]>,
"Max" <[email protected]> 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>

Thanks, Domenic !
Another intuitive guess bites the dust .. <bg>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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
[email protected]
"Sam via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> 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 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=""),TRANSPOSE(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