 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / July 2007#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 One way: =IF(COUNT(A1:A31)>0,AVERAGE(A1:A31),0) > Hi wondering if anyone can help me with this one... > [quoted text clipped - 12 lines] > sure must be a plain and simple answer to this.. > Dean Try this: =IF(COUNT(A1:AE1)=0,0,AVERAGE(A1:AE1)) Hope this helps. Pete > Hi wondering if anyone can help me with this one... > [quoted text clipped - 12 lines] > sure must be a plain and simple answer to this.. > Dean =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 > Hi wondering if anyone can help me with this one... > [quoted text clipped - 12 lines] > sure must be a plain and simple answer to this.. > Dean spot on - works a treat guess i was missing the most obvious way thanks again > =if(count(a1:a5)=0,"",average(a1:a5)). > This will first check if there are any numbers in the range. If not, leave [quoted text clipped - 17 lines] > > sure must be a plain and simple answer to this.. > > Dean 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. > Hi wondering if anyone can help me with this one... > [quoted text clipped - 13 lines] > sure must be a plain and simple answer to this.. > Dean 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 > If your data is in cells A1 through AE1, 31 cells, then you could use: > =IF(ISERROR(AVERAGE(A1:AE1)),0,AVERAGE(A1:AE1)) [quoted text clipped - 20 lines] >> sure must be a plain and simple answer to this.. >> Dean Another way: =IF(COUNT(A1:AE1),AVERAGE(A1:AE1),0)  SignatureBiff Microsoft Excel MVP > If your data is in cells A1 through AE1, 31 cells, then you could use: > =IF(ISERROR(AVERAGE(A1:AE1)),0,AVERAGE(A1:AE1)) [quoted text clipped - 20 lines] >> sure must be a plain and simple answer to this.. >> Dean |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.