Q131340 - Office Assistant Expense Report Contains Incorrect Formula

Office Assistant Expense Report Contains Incorrect Formula

Q131340

The information in this article applies to:

Microsoft Office Assistant for Windows, version 1.0
Microsoft Office for Windows, versions 4.2, 4.3
Microsoft Excel for Windows, versions 5.0, 5.0c

SYMPTOMS

In the Office Assistant Expense Report template (OAEXPRPT.XLT), the Lodging & Meals Total will not display a total if you do not enter a value for any of the meals.

CAUSE

The formulas in the total row for Lodging & Meals are incorrect. For example, the formula in cell C26 is:


   =IF(OR(C24>0,C18>0),C24+C19+C20,"")

If no meals are entered, the meals subtotal (C24) will not be greater than 0 and c18 will never be greater than 0, so the Lodging and Meals total formula will evaluate to "". The formula in cell c26 should read:


   =IF(OR(C24>0,C19>0),C24+C19+C20,"")

RESOLUTION

Most of the Microsoft Excel files included with Microsoft Office Assistant are templates, not regular workbooks. After you start Microsoft Excel, you can easily modify these templates. To open and modify the Office Assistant Expense Report template (OAEXPRPT.XLT), do the following:

From the File menu in Microsoft Excel, choose Open.

In the Microsoft Excel directory, select the template name that you want to modify. In this case, select OAEXPRPT.XLT.

Press and hold down the SHIFT key, and choose the OK button. This will open the template so that it can be edited.

NOTE: If you do not press and hold down the SHIFT key, a copy of the template is opened instead of the actual template file.

Select the Template tab.

Microsoft Excel templates included in Microsoft Office Assistant are protected. To unprotect the worksheet, choose Protection from the Tools menu, and then choose Unprotect Sheet.

Change the formula for Lodging & Meals total for Sunday (cell C26). The original formula is
```
      =IF(OR(C24>0,C18>0),C24+C19+C20,"") 
```
change the reference for cell C18 to cell C19 so that the formula looks like the following:
```
      =IF(OR(C24>0,C19>0),C24+C19+C20,"") 
```

Fill the formula for Sunday across to the Totals column.

To reprotect the worksheet, choose Protection from the Tools menu, and then choose Protect Sheet, and choose Save from the File menu.

From the File menu, choose Close.

The next time you open a file based on the template OAEXPRPT.XLT, you will see the changes you made and the sheet should be calculated correctly.

STATUS

Microsoft has confirmed this to be a problem in Office Assistant version 1.0 for Windows. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

For additional information on modifying Office Assistant Templates, please see the following article(s) in the Microsoft Knowledge Base:

Q122331 Office: Modifying Microsoft Office Assistant Templates

Additional query words:

Keywords :
Issue type :
Technology :