EXCEL STATISTICAL ANALYSIS

[Pages:134]EXCEL STATISTICAL Functions

Average vs. Weighted Average

Average

Weighted Average

Average Price

Sum of Prices / Count of Prices

Transaction Date

5/7/2007 6/7/2007 7/6/2007 8/9/2007

Item

Coffee Coffee Coffee Coffee

Price Per Unit $ 20 $ 25 $ 35 $ 30

Units Sold 500 750 200 300

Total:

20+25+35+30 = 110

Average Price: 110 / 4 = 27.50

Weighted Average Price

(Price per Unit * Units Sold) / Units Sold

Date

5/7/2007 6/7/2007 7/6/2007 8/9/2007

Item

Coffee Coffee Coffee Coffee

Price Per Units Unit Sold

$ 20 X 500 $ 25 X 750 $ 35 X 200 $ 30 X 300

+

500+750+200+300 = 1,750

= 10,000 = 18,750 = 7,000 = 9,000

+ 44,750

Weighted Average Price: 44,750 / 1,750 = $25.57

Weighted Average Exercise

"Weighted_Avg" sheet

Weighted Average Solutions

"Weighted_Avg" sheet

Wt. Avg. with SumProduct() Exercise

"Weighted_Avg_SumProduct" sheet

=SumProduct(Array1,Array2)

Multiplies corresponding numbers in two or more different ranges together then sums them.

Date

5/7/2007 6/7/2007 7/6/2007 8/9/2007

Item

Coffee Coffee Coffee Coffee

Price Per Unit $ 20 $ 25 $ 35 $ 30

Units Sold 500 750 200 300

Array 1 Array 2

20*500 = 10,000 25*750 = 18,750 35*200 = 7,000 30*300 = 9,000

44,750

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download