Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default Error Handling #N/A with AVERAGE Function - Average of values in Row


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
  #3   Report Post  
Biff
 
Posts: n/a
Default

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



  #4   Report Post  
Max
 
Posts: n/a
Default

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


  #5   Report Post  
Biff
 
Posts: n/a
Default

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






  #6   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default


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
  #7   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default


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
  #8   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default


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
  #9   Report Post  
Max
 
Posts: n/a
Default

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


  #10   Report Post  
Max
 
Posts: n/a
Default

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




  #11   Report Post  
Domenic
 
Posts: n/a
Default

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

  #12   Report Post  
Domenic
 
Posts: n/a
Default

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
  #13   Report Post  
Max
 
Posts: n/a
Default

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


  #14   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default


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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using The Average Function if a cell has NA carl Excel Worksheet Functions 6 May 21st 23 07:46 PM
Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM
Auto average function Diederik Excel Worksheet Functions 3 June 14th 05 03:56 PM
average function in Excel 2002 Sherry New Users to Excel 13 May 8th 05 01:49 PM
I want to use the MATCH function with the AVERAGE function but I . Miguel Excel Worksheet Functions 2 April 23rd 05 05:29 PM


All times are GMT +1. The time now is 10:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"