Tech2 Touch

Category: Excel Basics

  • Calculating Weighted Average in Excel

    Excel inbuilt Average function returns the average(or Mean) of a range of a data. However in many occasions we are required to calculate weighted average. Though Excel do not comes with a straight function to calculate weighted average. This can be done with the use of SUMPRODUCT and SUM functions. Let us start learning about weighted average with an example.

    Below is the sample data from a call center and let us calculate the Average Handle Time(AHT) for the day using different intervals calls and AHT.  The below data shows that initial hours calls were less and the AHT too was less. But as the call volume increased the AHT also increased.,

    Excel Formula Example

    The simple average formula =AVERAGE(C3:C9) returns 216.42. The problem with this is that it calculates average without considering the number calls. So we need to calculate average considering the number of calls received on each intervals. Ideally, the weighted average is the appropriate method to calculate like this calculations.

    So weighted average formula for the above example is =SUMPRODUCT(C3:C9,B3:B9)/SUM(B3:B9)
    This formula multiplies each AHT by its corresponding number calls and then adds all those products. The result is then divided by the number of days. This formula can be easily implemented to other types of calculations where weighted average is required.

  • Creating a Drop Down List in a Cell in Excel 2012

    There are situations, where we need to use restricted set of data in a list. When in data entry works are carried out by many people, chances are vast that small errors might occur. To overcome this, we can use drop down list in Excel. This not only restricts the user to enter the correct data also it helps to reduce the time to write it manually.  So for creating Drop down list, do we need to use Excel VBA feature? No;. Excel provides this feature in the form of data validation. So let us see how to create drop down list in Excel.
    Steps to Create Drop List in Excel:

    • Enter the list of items in a range
    • Select the cell that will contain the drop down list
    • Choose Data-> Data Tools->Data Validation
    • In the data validation dialog box, click the settings Tab
    • In the allow drop-down list select List.
    • In the source box specify the range that contains the items
    • Make sure that the In-Cell Drop down option is checked and Click OK
    • You are done. The drop down list is created in your desired Cell.

    Note:

    • If your list is small, then you can manually enter the list of items in the source box in the Data Validation dialog box.
    • You can copy paste the Drop down list created cell to other cells to carry the drop down list.