How to find average Price of a product bought at different Rate ?

Question

I have bought one bond in following denomination and price

Face Value 10 Crores @ Rs. 106.01
Face Value 280 lacs @ Rs. 100.515
Face Value 220 lacs @ Rs. 103.50
Face Value 260 lacs @ Rs. 102.20
Face Value 30 lacs @ Rs. 105.01

What will be average cost for my total purchase of Fave Value
1790 ?

what is the formula to find average price in excel sheet ?

Answer 1

(Rs106.01[10] + Rs100.515[280] + Rs103.50[220] + Rs102.2[260] + Rs105.01[30]) / 5
(Rs1,060.1 + Rs28,144.20 + Rs22,770.00 + Rs26,572.00 + Rs3,150.30) / 5
Rs81,696.60 / 5
Rs16,339.32 answer

In Excel type the following in a cell formated to numbers with 2 decimal places:
= ((106.01 * 10)+ (100.515 * 280) + (103.50 * 220) + (102.2 * 260) + (105.01 * 30)) / 5

The result will be 16,339.32 in the selected cell.

Answer 2

I don't know of any single-step function for producing this average in Excel. You need 3 columns, one for quantity, one for rate, and one for the extension (rate*qty). You also need to convert Crores to lacs or lacs to Crores for consistency. Sum the amount column and divide by the sum of the quantity column for your average.

1,000 106.010 Rs 106,010.00
. 280 100.515 Rs .. 28,144.20
. 220 103.500 Rs .. 22,770.00
. 260 102.200 Rs .. 26,572.00
. . 30 105.010 Rs . .. 3,150.30
1,790 . . . . . . . .. Rs186,646.50
Rs186,646.50 / 1,790 =
average = Rs 104.27

Answer 3

I don't understand the question, but in Excel, you can average a column by +average(a:b).