Office Assistant Expense Report Contains Incorrect Formula

Last reviewed: September 16, 1996
Article ID: 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:

   ARTICLE-ID: Q122331
   TITLE     : Office: Modifying Microsoft Office Assistant Templates

KBCategory: kbtool kbinterop
KBSubcategory: offasst
Additional reference words: 1.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 16, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.