|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 1st, 2007, 03:45 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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
May 1st, 2007, 03:51 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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
May 1st, 2007, 03:53 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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
May 1st, 2007, 03:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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
May 1st, 2007, 08:51 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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
July 15th, 2007, 10:14 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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
July 15th, 2007, 10:22 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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
July 15th, 2007, 10:39 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
