Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct() function resulted in an error #DIV/0
Hi, I found many people said it's useful to use sumproduct() function. In my case, I used it as below. Very bad results Can you correct it ONLY using sumproduct() function?
=SUMPRODUCT(--(A1:A1000="High"),--(C1:C1000<0),B1:B1000/C1:C1000) A B C 1 High 2 1 2 Low 2 2 3 High 3 2 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct() function resulted in an error #DIV/0
"AliceJ" wrote:
I used it as below. Very bad results Can you correct it ONLY using sumproduct() function? =SUMPRODUCT(--(A1:A1000="High"),--(C1:C1000<0),B1:B1000/C1:C1000) A B C 1 High 2 1 2 Low 2 2 3 High 3 2 There might be multiple problems. The root cause might be that some of C1:C1000 are empty cells. As such, Excel treats them as zero in this context. That precipitates the #DIV/0 error obviously. You might think the conditional test --(C1:C1000<0) prevents the evaluation of corresponding B1:B1000/C1:C1000, but it does not. Instead, Excel creates an array 1 or 0 (true or false) based on the evaluation of --(C1:C1000<0). And Excel creates an array of values B1:B1000/C1:C1000. Then SUMPRODUCT multiplies the corresponding elements of each array. We don't get that far because of the #DIV/0 errors when creating the last array. Try the following array-entered formula instead (press ctrl+shift+Enter instead of just Enter): =SUM(IF(A1:A1000="High",IF(C1:C1000<0,B1:B1000/C1:C1000))) |
#3
|
|||
|
|||
Hi
I see you have come up with a different user name and using a different forum. You have posted the same question twice to my knowledge in ExcelForum. com and answers have been posted. But you still try to post on other forums. Is it because you don't understand your question or you don't understand Excel! For anyone who is interested, here are two of the user names this person has used! http://www.excelforum.com/excel-form...-is-wrong.html http://www.excelforum.com/excel-form...-is-wrong.html They have also posted at Mr Excel. QUOTE=AliceJ;1608659]Hi, I found many people said it's useful to use sumproduct() function. In my case, I used it as below. Very bad results Can you correct it ONLY using sumproduct() function? =SUMPRODUCT(--(A1:A1000="High"),--(C1:C1000<0),B1:B1000/C1:C1000) A B C 1 High 2 1 2 Low 2 2 3 High 3 2[/quote] Last edited by Kevin@Radstock : January 17th 13 at 06:58 AM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding worksheets using sumproduct function gives error | Excel Discussion (Misc queries) | |||
Error while using SUMPRODUCT function | Excel Discussion (Misc queries) | |||
pleas help: sumproduct function returns #value or #ref error | Excel Worksheet Functions | |||
sumproduct function returns #value or #ref error | Excel Worksheet Functions | |||
deleting a macro resulted in a problem | Excel Discussion (Misc queries) |