An Average that Factors In Blank Cells

When you use the AVERAGE function, it ignores blank cells in the range you are averaging. If you want the blank cells to be included, then you need to look to one of the formulas presented in this tip.

Read this tip »

(Thanks to Paul Hignett, Ryszard Raciborski, Steve Aprahamian, Steve Sucher, Alex Blakenburg, Michael Avidan (MVP), Alan Cannon, Brian Dorey, Herman van Dam, John Gent, James Woolley, Andrija Vrcan, Barry Pegram, Elliot Penna, Walter Kumar, Wm Field, Dick Downey, Kevin Keyser, Bob Beechey, Jacques Raubenheimer, Peter McNab, Thomas Reeves, Fred van er Meulen, Ed de Jong, Neil Woller, James Wolz, Yvan Loranger, David Watssman, Paul Harvitz, and Patrick Sedgwick for contributing to this tip.)

Exact Matches with DSUM

The DSUM function is very handy when you need to calculate a sum based on data that matches criteria you specify. If you don't specify the criteria just right, however, you may not get the results you expect.

Read this tip »

Baffled by Array Formulas?

Have you heard about the special array formulas you can create in Excel? They make it a snap to perform calculations using large amounts of data and even generate results you can't get with regular formulas. Array formulas may be Excel's best-kept secret. Remove the confusion; discover how to use array formulas today.

Excel Refuses to Put Page Breaks between Subtotal Groups

Page breaks not appearing where you expect them in your subtotaled data? It could be because of a setting you made in your page setup.

Read this tip »

Leaving Trace Precedents Turned On

The Trace Precedents auditing tool can be quite helpful in seeing which cells "feed into" a particular formula. The results displayed by the tool cannot be saved with the workbook, but you can create a macro that will display precedents whenever a workbook is opened.

Read this tip »

This section is for those having problems making Excel behave. If Excel is giving you fits, feel free to submit your own Help Wanted question.

If you have a solution for the problems below, click the link after the problem to send us your answer. (All responses become the sole property of Sharon Parq Associates, Inc., and can be used in any way deemed appropriate.) If your response is used in a future issue, you will be credited for your contribution to the answer.

Using GROUPBY to Create a Summary Table

I have a worksheet that includes raw customer sales transaction data for the western division of our company. Column E contains the state in which each customer lives, and column G contains the order amount for each transaction. I've been told that I could use the new GROUPBY function to create a simple summary table that shows sales by state. I'm unclear on exactly how to do this, though.
—Tressa Williams (provide an answer for this Help Wanted question)