Weighted Average & SUMPRODUCT

Bill Lafferty

Is SUMPRODUCT supported by SmartSheet?  If so, what would the syntax look like?

Thanks.

Bill

J. Craig Williams

William,

UPDATE:

My previous answer (removed) was not correct.The SumProduct is 

=AVGW(array of numbers, array of weights) * SUM (array of weights)

 

Craig

 

Bill Lafferty

I did find a workaround that worked for me.

To find a weighted average, follow these steps:

 

1. In a new worksheet, type the following data:

   A1: Cost B1: Cases A2: $.20 B2: 10 A3: $.30 B3: 40

2. The SUMPRODUCT formula below that you would normally use in excel would not work as it is not supported in SmartSheet.  

   =SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3)

3. So, I used this formula which accomplishes the same thing (just had to hide a few colums): 
   
   
   
   =((A2*B2)+(A3*B3))/SUM(B2:B3)

Yordan

Indeed, the MS formula divides rather than to multiply.

=SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3)



or 



=((A2*B2)+(A3*B3))/SUM(B2:B3)

Christian Massey

I just wanted to comment here because this is the top search result for "SUMPRODUCT Smartsheet" in google. This is not related to weighted averages (which in the past I've accomplished using multiple countifs/sumifs and multiplication/division depending on what I'm trying to accomplish).

 

SUMPRODUCT in excel multiplies the values in arrays and then sums those products. The arguments for SUMPRODUCT can be equations, functions, and comparisons which are translated into boolean values.

An example:

Column A values: True, False, True, False, True

Column B values: Big, Small, Small, Big, Big

Column C values: 10, 9, 8, 9, 10

Column D values: 5, 6, 7, 6, 5

SUMPRODUCT(--(A1:A5="True"),--(B1:B5="Big"),(C1:C5-D1:D5))

 

The excel sheet translates this into the following arrays:

{1, 0, 1, 0, 1}, {1, 0, 0, 1, 1}, {5, 3, 1, 3, 5}

Multiplies the terms together like so:

{1 * 1 * 5, 0 * 0 * 3, 1 * 0 * 1, 0 * 1 * 3, 1 * 1 * 5}

Sums the array:

{5 + 0 + 0 + 0 + 5} = {10}

(Note: Excel can be finicky based on formatting of the cells)

To my knowledge there isn't a way to do this in Smartsheet directly, because you can't evaluate the delta between column C & D in the same formula as the sum.

 

What you CAN do is add a column for the delta between those values, and then use a SUMIFS statement to sum those values only if A = True and B = Big.

 

Continuing from the above example:

Add Column E

Column E values: "=[Column C]1-[Column D]1" etc.

Formula: "=SUMIFS([Column E]:[Column E], [Column A]:[Column A], "TRUE", [Column B]:[Column B], "Big")"

 

Adding calculation columns is often the solution in Smartsheet. I suggest locking and hiding those if other people may be using your sheets!

 

Hope that's helpful for anyone who reads this in the future.

 

If anyone from Smartsheet is reading this, matrix operations would be great.