Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default #div/0 errors and blank cells

Hi wondering if anyone can help me with this one...

Have 31 cells in a row which i want to get the average value from, some of
these 31 cellsmay or may not have data in them. The end coloum would be the
one working out the average value of that row of cells.

If no data is entered into any of the cells is it possible to get the
average formular not to return a #div/0 error but instead just return a 0
figure?
Obviously as soon as any data is entered the formular would work as normal
but just want it not to return the error should no data be entered.

The average figures obtained would then be used in a graph.

sure must be a plain and simple answer to this..
Dean
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default #div/0 errors and blank cells

One way:

=IF(COUNT(A1:A31)0,AVERAGE(A1:A31),0)

In article ,
dinouk wrote:

Hi wondering if anyone can help me with this one...

Have 31 cells in a row which i want to get the average value from, some of
these 31 cellsmay or may not have data in them. The end coloum would be the
one working out the average value of that row of cells.

If no data is entered into any of the cells is it possible to get the
average formular not to return a #div/0 error but instead just return a 0
figure?
Obviously as soon as any data is entered the formular would work as normal
but just want it not to return the error should no data be entered.

The average figures obtained would then be used in a graph.

sure must be a plain and simple answer to this..
Dean

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default #div/0 errors and blank cells

Try this:

=IF(COUNT(A1:AE1)=0,0,AVERAGE(A1:AE1))

Hope this helps.

Pete

On May 1, 3:45 pm, dinouk wrote:
Hi wondering if anyone can help me with this one...

Have 31 cells in a row which i want to get the average value from, some of
these 31 cellsmay or may not have data in them. The end coloum would be the
one working out the average value of that row of cells.

If no data is entered into any of the cells is it possible to get the
average formular not to return a #div/0 error but instead just return a 0
figure?
Obviously as soon as any data is entered the formular would work as normal
but just want it not to return the error should no data be entered.

The average figures obtained would then be used in a graph.

sure must be a plain and simple answer to this..
Dean



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 171
Default #div/0 errors and blank cells

=if(count(a1:a5)=0,"",average(a1:a5)).
This will first check if there are any numbers in the range. If not, leave
the result blank; if so, average those numbers.
--Bruce

"dinouk" wrote:

Hi wondering if anyone can help me with this one...

Have 31 cells in a row which i want to get the average value from, some of
these 31 cellsmay or may not have data in them. The end coloum would be the
one working out the average value of that row of cells.

If no data is entered into any of the cells is it possible to get the
average formular not to return a #div/0 error but instead just return a 0
figure?
Obviously as soon as any data is entered the formular would work as normal
but just want it not to return the error should no data be entered.

The average figures obtained would then be used in a graph.

sure must be a plain and simple answer to this..
Dean

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default #div/0 errors and blank cells

spot on - works a treat guess i was missing the most obvious way

thanks again

"bpeltzer" wrote:

=if(count(a1:a5)=0,"",average(a1:a5)).
This will first check if there are any numbers in the range. If not, leave
the result blank; if so, average those numbers.
--Bruce

"dinouk" wrote:

Hi wondering if anyone can help me with this one...

Have 31 cells in a row which i want to get the average value from, some of
these 31 cellsmay or may not have data in them. The end coloum would be the
one working out the average value of that row of cells.

If no data is entered into any of the cells is it possible to get the
average formular not to return a #div/0 error but instead just return a 0
figure?
Obviously as soon as any data is entered the formular would work as normal
but just want it not to return the error should no data be entered.

The average figures obtained would then be used in a graph.

sure must be a plain and simple answer to this..
Dean



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 146
Default #div/0 errors and blank cells

If your data is in cells A1 through AE1, 31 cells, then you could use:
=IF(ISERROR(AVERAGE(A1:AE1)),0,AVERAGE(A1:AE1))
The ISERROR function will catch any kind of error, not just division by zero
, #DIV/0.


"dinouk" wrote in message
...
Hi wondering if anyone can help me with this one...

Have 31 cells in a row which i want to get the average value from, some of
these 31 cellsmay or may not have data in them. The end coloum would be
the
one working out the average value of that row of cells.

If no data is entered into any of the cells is it possible to get the
average formular not to return a #div/0 error but instead just return a 0
figure?
Obviously as soon as any data is entered the formular would work as normal
but just want it not to return the error should no data be entered.

The average figures obtained would then be used in a graph.

sure must be a plain and simple answer to this..
Dean



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default #div/0 errors and blank cells

Are you answering old messages from old threads? Your last two postings seem
to be answering questions from back on May 1st and May 2nd. Is that how your
newsreader is showing them to you?

Rick


"Dave Thomas" wrote in message
. net...
If your data is in cells A1 through AE1, 31 cells, then you could use:
=IF(ISERROR(AVERAGE(A1:AE1)),0,AVERAGE(A1:AE1))
The ISERROR function will catch any kind of error, not just division by
zero , #DIV/0.


"dinouk" wrote in message
...
Hi wondering if anyone can help me with this one...

Have 31 cells in a row which i want to get the average value from, some
of
these 31 cellsmay or may not have data in them. The end coloum would be
the
one working out the average value of that row of cells.

If no data is entered into any of the cells is it possible to get the
average formular not to return a #div/0 error but instead just return a 0
figure?
Obviously as soon as any data is entered the formular would work as
normal
but just want it not to return the error should no data be entered.

The average figures obtained would then be used in a graph.

sure must be a plain and simple answer to this..
Dean




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default #div/0 errors and blank cells

Another way:

=IF(COUNT(A1:AE1),AVERAGE(A1:AE1),0)

--
Biff
Microsoft Excel MVP


"Dave Thomas" wrote in message
. net...
If your data is in cells A1 through AE1, 31 cells, then you could use:
=IF(ISERROR(AVERAGE(A1:AE1)),0,AVERAGE(A1:AE1))
The ISERROR function will catch any kind of error, not just division by
zero , #DIV/0.


"dinouk" wrote in message
...
Hi wondering if anyone can help me with this one...

Have 31 cells in a row which i want to get the average value from, some
of
these 31 cellsmay or may not have data in them. The end coloum would be
the
one working out the average value of that row of cells.

If no data is entered into any of the cells is it possible to get the
average formular not to return a #div/0 error but instead just return a 0
figure?
Obviously as soon as any data is entered the formular would work as
normal
but just want it not to return the error should no data be entered.

The average figures obtained would then be used in a graph.

sure must be a plain and simple answer to this..
Dean





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
"If" statement returning blank gives downstream formula errors. Johnboy Excel Discussion (Misc queries) 2 February 26th 07 05:26 PM
summing cells that have VALUE errors in them NewKid Excel Worksheet Functions 3 February 22nd 07 11:39 PM
Count cells with numbers and ignore cells with errors WonderingaboutMicrosoft Excel Discussion (Misc queries) 6 December 10th 06 08:03 PM
Sum some cells in column but not #n/a errors Unicorn Excel Discussion (Misc queries) 7 November 27th 06 04:37 PM
Imported Data creates blank cells that aren't really blank JackieD Excel Worksheet Functions 14 February 23rd 06 12:57 AM


All times are GMT +1. The time now is 07:10 PM.

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"