I have the following array formula that works properly in my worksheet:
{=SUMPRODUCT(IF(C14:P14,(C13:P13/C14:P14)/($C$1:$P$1),""),(C14:P14))/SUM(C14:P14)}
There are 2 problems with having this formula in the worksheet. First, it will be copied down a column hundreds of times. This will cause very slow processing with all of these array formulas in the sheet. Second, once the value is calculated, the result should remain static, even though some of the reference values may change in the future. So, I was going to place the formula in the middle of some other code and simply paste the result in the appropriate cell, but kept getting errors when I tried to convert the formula into VBA.
I thought I had it with this, but I get a 'type mismatch error'
{=SUMPRODUCT(IF(C14:P14,(C13:P13/C14:P14)/($C$1:$P$1),""),(C14:P14))/SUM(C14:P14)}
There are 2 problems with having this formula in the worksheet. First, it will be copied down a column hundreds of times. This will cause very slow processing with all of these array formulas in the sheet. Second, once the value is calculated, the result should remain static, even though some of the reference values may change in the future. So, I was going to place the formula in the middle of some other code and simply paste the result in the appropriate cell, but kept getting errors when I tried to convert the formula into VBA.
I thought I had it with this, but I get a 'type mismatch error'
Code:
ProdPercent = WorksheetFunction.SumProduct((Range("C13:P13") / Range("C14:P14")) / Range("C1:P1"), Range("C14:P14")) / WorksheetFunction.Sum(Range("C14:P14"))