Injecting a VAR (variance) calculation in a Sumproduct formula

[Ron0813]

Hi All -

Sort of a conceptual question here but if anyone has thoughts/advice it may save me some time.

I'm using the Sumproduct formula below to filter a table for certain rows (base on values from a lookup table), make a calculation, and then sum the results. Typical stuff for Sumproduct.

=SUMPRODUCT(Test_Data_Sales_Price_Per_Unit,Test_Data_Quantity,--(LOOKUP(Test_Data_Item_Code,Master_Data_Item_Code,Master_Data_Category)="A"))

What I'm trying to do now is calculate a variance on the values calculated by Sumproduct before the sum is done. I'm wondering if there is a way to insert a VAR formula at some point to operate on those values. Or possibly save them and then feed them to VAR.

Hope this makes sense and appreciate any thoughts.

Thanks!

 

[sijpie]

If the output of the VAR function is (or can be) an array, then in theory it can be done.