0206 SUMPRODUCT() for Weighted Average Computation

3 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$195.60
You save:  A$55.89
List Price:  ৳11,872.36
You save:  ৳3,392.34
List Price:  CA$187.37
You save:  CA$53.54
CHF 91.26
List Price:  CHF 127.77
You save:  CHF 36.51
List Price:  kr884.75
You save:  kr252.80
List Price:  €118.74
You save:  €33.93
List Price:  £107.27
You save:  £30.65
List Price:  HK$1,084.99
You save:  HK$310.02
List Price:  ₹10,502.85
You save:  ₹3,001.03
List Price:  RM586.20
You save:  RM167.50
List Price:  ₦52,986.21
You save:  ₦15,140
List Price:  kr1,265.84
You save:  kr361.69
List Price:  NZ$211.96
You save:  NZ$60.56
List Price:  ₱6,870.27
You save:  ₱1,963.07
List Price:  ₨23,504.32
You save:  ₨6,716
List Price:  S$192.17
You save:  S$54.91
List Price:  ฿4,370.86
You save:  ฿1,248.90
List Price:  ₺1,021.10
You save:  ₺291.76
List Price:  R2,469.09
You save:  R705.50
Already have an account? Log In


Hello everybody, this time we talk about some product formula and its applications in the area of finance, accounting and costing. To give you a brief background about the applications, let me just walk you through a simple example. I purchase a product in unfold different quarters. So first quarter, I purchased 100, lots second quarter, 500, lots, third quarter 250, lots and fourth quarter 120 lots. Now the price has been varying over the last four quarters. So in the first quarter, I purchased the lots at 1200 dollars per block.

The prices declined a bit, but then again, it increased a bit and it continued the trend. Now if somebody asked you to find a weighted average cost per lot, how would you proceed? using the traditional method, you would first see what is the total price that you had paid for the entire purchases, so you would multiply 100 lot with 1200 dollars. As you copy paste to the next three cells, you get the four numbers the total money paid next You probably are going to press ALT equal to to initiate the sum formula to add up all the purchase amount, this is the total purchase amount for the entire year. Now, you perhaps also want to find out what are the total purchase lots that have taken place. So in total, how many lots purchased 970.

Finally, you come at a sell and say that look, whatever amount I had paid that is, in this case, $1.15 million divided by the total loss that you had received against them. So this entire procedure leads you to weighted average cost per a lot. It appears to be so cumbersome why because you have to move in so many different steps. Compare this with the technique, which I'm about to show to you. In fact, let me do one thing. Let me delete this final answer 1185.

And let me write a formula. And I wish to write a formula single formula, which gives me consolidated answer without using this intermittent calculation, which I'm deleting one by one Same mother says I write equals su MP. And that will give me some select in the drop down. Let me press the Tab key, and it tells me are a one or a two or a three. So basically it is asking me to choose multiple arrays of data. Now how do I choose that I choose array one.

The formula parameter tells me to put a comma i do let me choose the next array, noting that the height of the arrays are same. Now what is it going to do? It is going to pick up the first value of first array first value of secondary I'm going to multiply that it is going to do the same thing for second value of the first area and second value of the secondary and multiply same. Now eventually it will add up all the values and you will get the same exact answer as you were getting when you had calculated total purchase price for all the four quarters combined. Watch this is not the final answer. This only gives you the sum product product first Then finally sum of all the values that you've received.

Now, this total amount if I divide by the sum of the total lots you received against it. And if I do that, I get exactly the same answer which you had arrived at after calculating through multiple other steps. So, this is a quick formula sum product quite often used to find the weighted average calculation. In fact, there are occasions where this is also used with array formula, details of which I'll discuss in the next few sections. Till then practice this till this point

Sign Up