Ignoring #DIV/0 in formulas Microsoft Excel 2003 (full product)
March 18, 2010 at 10:21:15
Specs: Windows XP
 I am using this formula but I need up to date information but will not display current information due to #DIV/0 errors. Need some assistance please.Current Formula:=SUM(E2:E47)/SUMPRODUCT((E2:E47<>0)*ISNUMBER(E2:E47)) See More: Ignoring #DIV/0 in formulas

#1 March 18, 2010 at 10:59:16
 Is the issue that you do not want to see #DIV/0 errors when they occur or that you shouldn't be getting any #DIV/0 errors?The first part is easy to fix. Just put your formula inside an IF(ISERROR()) statement:=IF(ISERROR(SUM(E2:E47)/SUMPRODUCT((E2:E47<>0)*ISNUMBER(E2:E47))),"",SUM(E2:E47)/SUMPRODUCT((E2:E47<>0)*ISNUMBER(E2:E47)))If you are not trying to just hide #DIV/0 errors, but eliminate them, you'll need to determine what is causing them. Obviously this part of your formula is evaluating to 0:=SUMPRODUCT((E2:E47<>0)*ISNUMBER(E2:E47))Since we can't see your data, it would be hard for us to suggest a way to fix that.Have you tried to Single Step through the formula using Tools...Formula Auditing...Evaluate Formula to see if you can determine where the problem is?

Report •

#2 March 18, 2010 at 11:09:28
 Hi,Your formula calculates the sum of all the values in the given range and then divides that total by the number of cells that contain numbers, other than zero.The division by zero error means that the SUMPRODUCT() function is returning zero.The most likely reason is that all the cells in the range E2:E47 contain text, not proper numbers. The cell contents display as numbers, and many Excel functions will treat them as numbers by doing an intrinsic conversion, but SUMPRODUCT isn't doing that.Try this function: =COUNT(E2:E47)If it returns zero, then there are no 'real' numbers in the range.If you get zero from the count function, try changing the cell contents to real numbers.In cell F2 enter this: =VALUE(E2)Drag the formula down to F47Copy F2 to F47Select E2 and Paste Special Paste valuesDelete the formulas in column FHope this helpsRegards

Report •
Related Solutions 