Weighted Average Function for Microsoft Excel

mbatookit
free Microsoft Excel macros, functions and resources for the MBA

Another great website from

Weighted Average

Returns the weighted average of a set of numbers, with each number receiving a relative weighting in the calculation specified in a second set of numbers.

Syntax (Values in boldface are required; others are optional)

WeightedAvg(ValuesRange, WeightsRange)

ValuesRange: The values to be averaged. Enter as a range.
WeightsRange: The relative weights assigned to the values in calculating the average. Enter as a range. WeightsRange must be of the same size as ValuesRange.

Example

In the example below, a firm has ninety employees across three salary levels. The simple average of the three salary levels ((750000+350000+125000)/3) is $408,333, which might be a useful number to toss out at associate recruiting events on campus, but doesn't give a meaningful indication of the firm's real compensation cost per employee. The average of the three salary levels, weighted by the number of employees at each level, tells the real story.

Code

To install this function, copy the entire contents of the window below into your power4XL code module in Excel's Visual Basic editor.

For detailed instructions, see the "Individual Function Setup" page.

To copy the contents to your clipboard, place your cursor inside the window below, then press "CTRL+C".

NOTE: By copying the code below, you are agreeing to the power4XL license terms. You agree to use the code only on your personal and/or business computer(s) for your own personal use. You agree not to distribute, publish, alter, or edit the code in any way. You may freely refer others to this website if you wish to make the code available to them.

Function WeightedAvg(Values As Range, Weights As Range) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. MyCounter = 0 MySumProduct = 0 MySum = 0 For Each MyValue In Values MyCounter = MyCounter + 1 MySumProduct = MySumProduct + (MyValue * Weights(MyCounter)) MySum = MySum + Weights(MyCounter) Next WeightedAvg = MySumProduct / MySum End Function

Related Information from Sponsors

Home :: Functions :: Installation :: Help :: Contact :: Testimonials :: Links & Resources

Copyright notice: power4xl.com and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley. License is granted for individual users to download examples and to copy code directly into user's Excel spreadsheets and Visual Basic for Applications files. Users may not redistribute code in any way. Providing hyperlinks to this website is encouraged, but posting power4XL code and examples on other websites is expressly forbidden. "Microsoft" is a registered trademark of Microsoft Corporation. Neither power4xl.com nor Fritz Dooley are affiliated with Microsoft Corporation.