 |
 | 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 / New Users / February 2007sum cells with numbers
This shouldn't be too diffacult but I can't seem to find the function that would allow me to sum the cells with numbers and then divide that by the number of blank cells. e.g. cells A1:A5, if A1 is 5, A2 is 10 and cells A3 through A5 are blank then the answer would be 5. 5+10/3=5 I tried isnumber and isblank but that doesn't work. Any ideas? What result do you want if all the cells contain numbers? Biff > This shouldn't be too diffacult but I can't seem to find the function that > would allow me to sum the cells with numbers and then divide that by the > number of blank cells. > e.g. cells A1:A5, if A1 is 5, A2 is 10 and cells A3 through A5 are blank > then the answer would be 5. 5+10/3=5 > I tried isnumber and isblank but that doesn't work. Any ideas? A play using COUNTBLANK might suffice: =IF(COUNTBLANK(A1:A5)=0,"",SUM(A1:A5)/COUNTBLANK(A1:A5)) If there's no blank cells, it'll return a blank: ""  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > This shouldn't be too difficult but I can't seem to find the function that > would allow me to sum the cells with numbers and then divide that by the > number of blank cells. > e.g. cells A1:A5, if A1 is 5, A2 is 10 and cells A3 through A5 are blank > then the answer would be 5. 5+10/3=5 > I tried isnumber and isblank but that doesn't work. Any ideas? Hi Assuming you just want the sum of the numbers if there are no blanks in the range, then one way would be =SUM(A1:A5)/MAX(1,COUNTIF(A1:A5,"")) SignatureRegards Roger Govier > This shouldn't be too diffacult but I can't seem to find the function > that [quoted text clipped - 5 lines] > then the answer would be 5. 5+10/3=5 > I tried isnumber and isblank but that doesn't work. Any ideas? |
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.