Use an Excel array function to create an AverageIf function
Thursday, February 05, 2009 03:23 PM
Excel has SumIf and CountIf, but no AverageIf. However, with a little array magic, you can get Excel to average values, conditionally.
Microsoft Excel
Use an Excel array function to create an AverageIf function
Excel has SumIf and CountIf, but no AverageIf. However, with a little array magic, you can get Excel to average values, conditionally.
Simply insert the following form as an array: =AVERAGE(IF(conditionalrange = condition, averagerange)
(To enter an array function, press [Ctrl]+[Shift]+[Enter] instead of just [Enter].)
You can test this function by entering a column of values and comparing the results of AVERAGE() to the array. The AVERAGE() function in A7 evaluates all the values in column A. In contrast, the array (cell C7) evaluates only those values that are less than 10.
The form is versatile enough to handle conditions from the worksheet. For instance, the array function below averages values in column A only when the value in column B is the letter A.
Microsoft Word
Lightning-quick mouse tricks for accessing Word formatting options
If you spend a fair bit of time hopping into dialog boxes to tweak the appearance of your text or documents, there are some double-click tricks you might find useful. Here's a sampling of the most common ones. (For a more comprehensive list, see 34 timesaving mouse tricks for Word users.)
- To open the Page Setup dialog box, double-click on the horizontal ruler (the gray part) or the vertical ruler (any part).
- To open the Paragraph dialog box, double-click on an indent marker on the horizontal ruler.
- To open the Tabs dialog box, double-click on the horizontal ruler (the white part). This will also set a tab at the spot where you clicked. If you don't want to set a new tab, you can click an existing tab marker.
- To access bullet or number options, double-click on a bullet character or number in a bulleted or numbered list.
- To open the Table Properties dialog box, double-click the Move Table Column marker (those little dotted squares you see on the horizontal ruler when you're in a table) or the Table Move Handle (the four-headed arrow that appears at the top-left corner of a table in Print Layout view).
- To open the Page Setup dialog box with This Section selected in the Apply To drop-down list, double-click a section break mark. "This" refers to the section above the section break mark you clicked on.
- To open the AutoShape dialog box, double-click on an AutoShape.
- To open the Format Picture dialog box, double-click on a picture or clip art object.
- To open the Format Text Box dialog box, double-click on the frame of a text box.
Microsoft Access
Track down problems more easily by including properties in Access report footers
When a printed report contains questionable data, your first instinct may be to question the record source. However, when you ask for the report's name and database table, users will often give you the report title from the printout, which is entirely different from the actual name of the report file.
Rather than guess the actual report filename and record source, have Access add this information to the report footer when the report is printed. Follow these steps:
- Open the report in Design view.
- Click the Text Box control tool and then click and drag in the Report Footer section of the report.
- Select the text in the Label control, type Report Name:, and format it as desired.
- Click in the text box and enter the following formula:
=[Name]
- Click the Text Box control tool and then click and drag in the Report Footer section of the report.
- Select the text in the Label control, type Record Source,: and format it as desired.
- Click in the text box and enter the following formula:
=[Record Source]
Figure A shows how the report footer will look.
Figure A
When you run the report, Access will output the correct name of the report file and its record source from the properties box in the Report footer, as shown in Figure B.
Figure B
There are currently no comments for this post.