Avoid nested formula errors with Excel's Insert Function dialog box

by Mary Ann Richardson | Dec 19, 2006 8:00:00 AM

Tags: Microsoft Office, Mary Ann Richardson, Click OK, cell, Microsoft Excel, Microsoft Office Suite Excel Tips Newsletter

Takeaway: Excel's nested formulas option may be useful, but many people find it a challenge fraught with potential errors. Mary Ann Richardson explains an easy way around it: use the Insert Function dialog box.

Entering nested formulas manually into Excel can often lead to syntax and typing errors. For example, suppose you want to know what the average minimum monthly sales are for the North, South, and East regions. To avoid errors, you could create a MIN formula (MIN being the minimum function, which returns the smallest value in a range, as opposed to MAX, which would return the largest value in a range) for each of the regions in three different cells, and then reference those cells in a separate average formula.

Or, you could follow these steps and use the Insert Function dialog box to nest the MIN formulas in one average formula.

1. Click in the cell where you want the formula to appear.
2. Click the Insert Function button in the formula bar.
3. Select Average and click OK.
4. Click the Arrow button to the left of the Cancel button in the formula bar.
5. Select MIN from the list.
6. Select the North region's monthly totals cells.
7. Click OK.
8. Click the Insert Function button.
9. Click in the Number 2 box.
10. Click MIN in the formula bar.
11. Select the South region's monthly totals cells.
12. Click OK.
13. Click the Insert Function button.
14. Click in the Number 2 box and tab to the Number 3 box.
15. Click MIN in the Formula bar.
16. Select the East region's monthly total cells.
17. Click OK.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.