Excel Magic Trick 476: SUMPRODUCT Function for Weighted Average Cost From Transaction Data
Loading...
Loading...
9,923
Loading...
Uploader Comments (ExcelIsFun)
see all
All Comments (7)
-
Your formula is great way to go:
=SUMPRODUCT((Col A)*(Col B),Col C)
-
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
-
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 "--" ?
Loading...
1:30
Excel Magic Trick 475: SUMPRODUCT for Bank Deposit Calculationby ExcelIsFun1,851 views- 1:44
Excel Magic Trick 477: 13 Digit Number ID Shows Scientific Notation!?by ExcelIsFun1,876 views
- 14:00
Excel Formula Efficiency 1: SUMPRODUCT function 12 Examplesby ExcelIsFun40,509 views
- 13:54
Excel Statistics 35: Weighted Mean & Geometric Meanby ExcelIsFun10,635 views
- 10:58
Excel Magic Trick 549: Dynamic Data Validation List Drop-Down OFFSET or Table Feature?by ExcelIsFun23,798 views
- 6:59
Inventory Managementby xTupleERP19,301 views
- 6:35
SUMPRODUCT Functionby sc0922706,810 views
- 5:37
Data Entry Trainingby solo207022,827 views
- 4:54
Excel Magic Trick 398: VLOOKUP with Two Lookup Values (IFERROR function also) 2 lookup valuesby ExcelIsFun36,357 views
- 10:52
Excel Magic Trick 497: Unit Inventory Total For Each Store SUMIFS or SUMPRODUCT functionsby ExcelIsFun19,544 views
- 11:33
Excel Magic Trick 538: Dynamic Sub Tables Based On Master Sheet Array Formula & Drilling Throughby ExcelIsFun12,023 views
- 2:49
Trend Forecasting in Excelby IntroToOM46,495 views
- 9:06
Excel Magic Trick 568: SUMIFS function Monthly Running Total from Transaction Databy ExcelIsFun7,983 views
- 1:40
Excel 2007 - Create Basic Inventory System using Form and Formulaby jiejenn80,862 views
- 5:00
Excel Magic Trick 514: Conditional Formatting Duplicate Values (3 Methods)by ExcelIsFun11,870 views
- 4:47
Excel Magic Trick 545: Count Between Dates with 3 Criteria COUNTIFS & DCOUNTA & SUMPRODUCTby ExcelIsFun5,028 views
- 7:22
Excel Magic Trick 400: Show List of Values In Cells After Using Data Validation Drop-Downby ExcelIsFun9,439 views
- 5:46
SUM SUMIF COUNT COUNTIF - Excel for Business - Lesson 3by raycolondotcom63,289 views
- 10:15
Excel Magic Trick #194: Grade Book Based on Percentagesby ExcelIsFun12,831 views
- 0:25
Mr Excel UnBanned From YouTubeby ExcelIsFun4,047 views
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?
payasoinfeliz 4 months ago
@payasoinfeliz , really busy at work. Try:
mrexcel [dot] com/forum
ExcelIsFun 4 months ago
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?
Parafasio 8 months ago
I do not know how to solve that. Try THE best Excel question site:
mrexcel[dot]com/forum
ExcelIsFun 8 months ago