Excel Magic Trick 476: SUMPRODUCT Function for Weighted Average Cost From Transaction Data

[payasoinfeliz]

Hi, Excel Is Fun. Do you know if there is a way to get an average time for the difference between multiple times? For example column A has start time, column B has end time, and there are various times across 100 rows, and I want the average difference of all rows. Is that possible?

[ExcelIsFun]

@payasoinfeliz , really busy at work. Try:

mrexcel [dot] com/forum

[Parafasio]

So what if you don't have the data arranged this way (where you have the quantity purchased at each price)? What if you have a huge database that has every registry? Is there a way to calculate the weighted average without arranging the data in this fashion?

[ExcelIsFun]

I do not know how to solve that. Try THE best Excel question site:

mrexcel[dot]com/forum

[oneggold]

Why not to make it as this:

=SUMPRODUCT((Col A)*(Col B),Col C) Then you don't need to make the DOUBLE -

Because, how do you know that you need to add "--" ?

[ExcelIsFun]

Any operation on arrays of TRUEs and FALSEs converts the T/F to 1s and 0s. *, +, ^, / or even --

The Double Negative is faster calculating. This only matters when you have huge data sets. But since it works the same I tend to teach my classes to just use the Double Negative.

Here are 2 videos that go into great detail about this topic:

Excel Formula Efficiency 1: SUMPRODUCT function 12 Examples

Excel Formula Efficiency 12: Fastest Formula In Excel

[ExcelIsFun]

Your formula is great way to go:

=SUMPRODUCT((Col A)*(Col B),Col C)