Microsoft Excel 2003 (full product)

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))

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?

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 F47

Copy F2 to F47

Select E2 and Paste Special Paste values

Delete the formulas in column FHope this helps

Regards

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History