Adding Weighted Averages to a PivotTable

PivotTables are used to boil down huge data sets into something you can more easily understand. They are very good simple aggregations, such as sums, counts, and averages. They can't do weighted averages, however. That doesn't mean you are out of luck; you can still calculate weighted averages using the ideas in this tip.

A good example of how to use calculated fields is for summarizing data differently than you can normally summarize it with a PivotTable. When you create a PivotTable, you can use several different functions to summarize the data that is displayed. For instance, you can create an average of data in a particular field. What if you want to create a weighted average? Excel doesn't provide a function that automatically allows you to do this.

When you have special needs for summations—like weighted averages—the easiest way to achieve your goal is to add an additional column in the source data as an intermediate calculation, and then add a calculated field to the actual PivotTable.

For example, you could add a "WeightedValue" column to your source data. The formula in the column should multiply the weight times the value to be weighted. This means that if your weight is in column C and your value to be weighted is in column D, your formula in the WeightedValue column would simply be like =C2*D2. This formula will be copied down the entire column for all the rows of the data.

You are now ready to create your PivotTable, which you should do as normal with one exception: you need to create a Calculated Field. Follow these steps:

1. Select any of the cells in the PivotTable.

2. Make sure the Options tab of the Ribbon is displayed.

3. In the Tools group, click the Formulas tool and then choose Calculated Field. Excel displays the Insert Calculated Field dialog box, shown earlier in Figure 20.

4. In the Name box, enter a name for your new field.

5. In the Formula box, enter the formula you want used for your weighted average, such as =WeightedValue/Weight. (You use field names in the formula; you can select them from the field list at the bottom of the Insert Calculated Field dialog box.)

6. Click OK.

Your calculated field is now inserted, and you can use the regular summation functions to display a sum of the calculated field; this is your weighted average.

Since there are many different ways that weighted averages can be calculated, it should go without saying that you can modify the formulas and steps presented here to reflect exactly what you need done with your data.

Have questions on this or any other Excel topic? Let us know! We hope this helps you become more efficient!

Posted courtesy of Tips.Net

How Does Excel Evaluate Formulas?

Ever wonder how Excel is evaluating a particular formula? It's easy to figure out if you use the Evaluate Formula tool…

It is often frustrating to figure out exactly how Excel arrives at a particular result—particularly if the formula returning the result is complex. Excel provides a very useful tool you can use to help determine out what is going on when Excel evaluates a formula.

To access this tool, display the Formulas tab of the ribbon. In the Formula Auditing group click the Evaluate Formula tool. Excel displays the Evaluate Formula dialog box.

At this point, Excel shows the full formula from the cell, and part of it is underlined. This underlined area represents the part of the formula that Excel will next evaluate. This allows you to see what intermediate steps Excel follows in arriving at a result. Every time you click the Evaluate button, Excel replaces the underlined portion of the formula with a result.

Nothing you do with the formula evaluator actually affects the formula in your worksheet; it remains unchanged. Instead, Excel simply shows you what happens as it works through each part of the formula to arrive at a result. When you are done using the formula evaluator, click the Close button.

Have questions on this or any other Excel topic? Let us know! We hope this helps you become more efficient!

Using the Keyboard to Change Cell Width and Height

Some people ask if there is a way to adjust the height and width of a cell by using the keyboard, without using the mouse. (Some people are really adverse to using the mouse!) The answer is yes, there is a way. The problem, however, is that it isn't a terribly intuitive way...

Here's how you do set the row height:

1. Press Alt. This kicks Excel into a "shortcut key mode" and you should see the shortcut keys appear above each tab of the ribbon.
2. Press H to indicate you want to use the Home tab. New shortcut keys appear above each tool in the tab.
3. Press O to indicate you want to use the Format tool in the Cells group. Excel displays a drop-down list of options.
4. Press H to choose the Row Height option. Excel displays the Row Height dialog box.
5. Type the value you want for the row height.
6. Press Enter.

The only difference in these steps when you want to specify the column width is that you should press W in step 4.

Here's another of changing the row height:

1. Press Shift+Spacebar. Excel selects the entire row.
2. Press Shift+F10 to display a Context menu. (This is the same menu you see if you were to right-click on the selection.)
3. Press R to indicate you want to change the row height. Excel displays the Row Height dialog box.
4. Type the value you want for the row height.
5. Press Enter.

A similar sequence will work for setting the column width:

1. Press Ctrl+Spacebar. Excel selects the entire column.
2. Press Shift+F10 to display a Context menu. (This is the same menu you see if you were to right-click on the selection.)
3. Press C twice to choose the second "C" command in the Context menu (Column Width).
4. Press Enter. Excel displays the Column Width dialog box.
5. Type the value you want for the column width.
6. Press Enter.

As always, please comment and/or contact me if you would like additional information on how we can help you Build Efficiency in your small business!

Pivot Tables - One of Excel's Most Powerful Tools

I won't try and hide the fact from you that I am a big fan of Pivot Tables. I use them a lot in the development of Spreadsheets for my clients. Once the client sees the Pivot Table, they nearly always ask "could I do that?" the answer of course is yes! Unfortunately most people tend to shy away from Pivot Tables, as they see them as too complex. to be honest with you, when you first use a Pivot Table the whole thing can seem a bit daunting. Don't be put off by this as persistence will almost certainly pay off. I find the best part about Pivot Tables is their ability to be manipulated via 'Trial-and-Error' and immediately show the result. If its' not what you expect, simply use the Undo feature and/or have another go! What you must always remember is that you are not changing the structure of your original table in any way at all, so you can do no harm!

What would we use them for?
We would use a Pivot Table to produce meaningful information from a table of information. Imagine you have a table of data that contains names, addresses, ages, occupations, phone numbers, postcodes etc. With a Pivot Table we could very easily and quickly find out:
1. How many people have the same names.
2. How many postcodes are the same.
3. A count of a particular occupation.
4. See only people that match a particular occupation.
5. Find out the addresses of people that match a postcode

In fact the list can go on and on!

What is the advantage ?
Perhaps the biggest advantage to using Pivot Tables is the fact that we can generate and extract meaningful information from a large table of information within a matter of minutes. Or perhaps it is because they will not use up a lot of memory from your PC. In a lot of cases we could get the same results from a table of data by using Excels built in functions, but this would take more time and use far more memory. On top of this, if we wanted some new information we can simply drag-and-drop (pivot). We can also opt to have our information update each time we open the Workbook and/or by clicking refresh.

What are macros and why are they useful?

Macros are a great productivity tool that allows you to run series of actions with a click of a button. Macros help you work efficiently.

What is a macro?
A macro is a set of tasks combined together so that you can run or replay the entire task together with a single command. Macros are a powerful productivity tool. With macros you can perform long or boring tasks just by a single click.

Macros are very popular with most of the Windows software.

Many applications provide macro capability inbuilt, like MS Excel, MS Word etc. These applications provide macro recording capabilities as well as some kind of macro language. For example: you can record a macro in Excel to perform calculations, format cells or just about anything you do in Excel.

What can you do with macros?
Tons. Anything and everything that is repetitive can be done using macros. All of us perform many repetitive actions day after day on our computers. Once you are aware that these are repetitive actions, you can easily create a macro for it and next time perform it with a click of a button.

Examples: Login and check your multiple mail accounts, format data, insert date and copyright notices onto your documents, generate reports, copy data between applications. There are a zillion things you can do.

If you think you are doing the same task again and again and it is frustrating and wasting your time and energy, you are ready to use macros. Even if it is not getting on your nerve, using a macro is a smart and fun way of working. Just understanding what you can do with macros will suggest new and more productive ways to get your job done.

Interested in seeing if a macro will work for your project, send us an email and we’ll review it!

Welcome to the new MN Excel Consulting site and blog!

After a summer-long redesign, our new site has launched. Follow MN Excel Consulting on Twitter (MNExcel) and become a fan of www.mnexcelconsulting.com on Facebook!

If you would like to see a topic discussed, let me know!