I have a sum product where I need to divide, so one of the inputs is 1/AL:AL. The problem is that some of those cells are 0. I have tried 1/if(AL:AL=0,1,AL:AL) and iferror(1/AL:AL,1) but both still return #DIV/0!. Here is my actual formula:
我有一个需要除的和积，其中一个输入是1/AL:AL。问题是有些单元格是0。我尝试过1/if(AL:AL=0,1,AL:AL)和iferror(1/AL:AL,1)，但它们仍然返回#DIV/0!这是我的实际公式:
=SUMPRODUCT('Data'!$I:$I,'Data'!$AV:$AV,**1/'Data'!$AL:$AL**)
2 个解决方案
#1
1
This should work for you:
这应该对你有用:
=SUMPRODUCT(Data!$I:$I,Data!$AL:$AL*(Data!$AV:$AV<>0)/((Data!$AV:$AV=0)+(Data!$AV:$AV<>0)*Data!$AV:$AV))
However, I highly recommended to never use full column references with array formulas or functions that calculate arrays. Sumproduct is a function that calculates arrays, and using whole column references can be very detrimental to the speed and efficiency of your workbook. Always try to use as limited a range as possible, for example the adjusted formula might look like this:
但是，我强烈建议不要使用带有数组公式或计算数组的函数的全列引用。Sumproduct是一个计算数组的函数，使用整个列引用会对工作簿的速度和效率非常不利。总是尽量使用有限的范围，例如调整后的公式可能是这样的:
=SUMPRODUCT(Data!$I$1:$I$10,Data!$AL$1:$AL$10*(Data!$AV$1:$AV$10<>0)/((Data!$AV$1:$AV$10=0)+(Data!$AV$1:$AV$10<>0)*Data!$AV$1:$AV$10))
If you are using whole column references in order to pick up new data as it is entered, please consider using dynamic named ranges or table references.
如果您使用整个列引用来获取输入的新数据，请考虑使用动态命名范围或表引用。
#2
0
Your formula with IFERROR(1/AL:AL,1) should work if you enter it as an array formula, with the cursor in the formula bar, press CTRL+SHIFT+ENTER.
如果您将IFERROR(1/AL:AL,1)的公式作为数组公式输入，并将光标放在公式栏中，那么按CTRL+SHIFT+ enter键就可以了。
#1
1
This should work for you:
这应该对你有用:
=SUMPRODUCT(Data!$I:$I,Data!$AL:$AL*(Data!$AV:$AV<>0)/((Data!$AV:$AV=0)+(Data!$AV:$AV<>0)*Data!$AV:$AV))
However, I highly recommended to never use full column references with array formulas or functions that calculate arrays. Sumproduct is a function that calculates arrays, and using whole column references can be very detrimental to the speed and efficiency of your workbook. Always try to use as limited a range as possible, for example the adjusted formula might look like this:
但是，我强烈建议不要使用带有数组公式或计算数组的函数的全列引用。Sumproduct是一个计算数组的函数，使用整个列引用会对工作簿的速度和效率非常不利。总是尽量使用有限的范围，例如调整后的公式可能是这样的:
=SUMPRODUCT(Data!$I$1:$I$10,Data!$AL$1:$AL$10*(Data!$AV$1:$AV$10<>0)/((Data!$AV$1:$AV$10=0)+(Data!$AV$1:$AV$10<>0)*Data!$AV$1:$AV$10))
If you are using whole column references in order to pick up new data as it is entered, please consider using dynamic named ranges or table references.
如果您使用整个列引用来获取输入的新数据，请考虑使用动态命名范围或表引用。
#2
0
Your formula with IFERROR(1/AL:AL,1) should work if you enter it as an array formula, with the cursor in the formula bar, press CTRL+SHIFT+ENTER.
如果您将IFERROR(1/AL:AL,1)的公式作为数组公式输入，并将光标放在公式栏中，那么按CTRL+SHIFT+ enter键就可以了。