Microsoft KB Archive/121020

= Using Automatic Calculations or Picking from List Calculations =

Article ID: 121020

Article Last Modified on 11/15/2004

-

APPLIES TO


 * Microsoft Works 3.0 Standard Edition
 * Microsoft Works 3.0a
 * Microsoft Works 3.0b
 * Microsoft Works 4.5 Standard Edition
 * Microsoft Works 4.5a
 * Microsoft Works 4.0 Standard Edition
 * Microsoft Works 4.0a

-



This article was previously published under Q121020



SUMMARY
Works for Windows does not have the ability to display a "pick from list" option. You can simulate this behavior using the CHOOSE function.

Type the available options in the database form as labels and assign each one of them a number from 0 to, sequentially, where is the number of options in your list. You will need two fields: one field in which to enter the number and the other field to calculate the text value to be presented.

Problem Description
You have a company with five employees:

Jan

Janet

David

Dave

Alan

You want the employees to fill out time cards, but only to include their names. You want the time cards to perform the following functions automatically:


 * Insert the employee's salary


 * Calculate a commission based on the salary, and a certain percentage that is possibly different for each employee.

Also, there are employees with similar names that might conflict (for example, Jan and Janet). You could use an IF statement, but this would be long.

Solution
  Assign an Employee number to each employee. (The employee will enter this in the form to prevent discrepancies with names). Also, assign the salary and commission percentage. For example:    NAME    Emp. Number   Salary      Commission Percentage

Jan         0         $100                  .5 Janet       1         $600                  .10 David       2         $100                  .7 Dave        3         $600                  .10 Alan        4         $100                  .5  Create the four fields below:

Field 1: Employee Number

Field 2: Employee Name

Field 3: Salary

Field 4: Commission

 In Field 2 (Employee Name), enter the following function:

=CHOOSE(Employee Number,"Jan","Janet","David","Dave","Alan")

 In Field 3 (Salary), enter the salary for each employee.

=CHOOSE(Employee Number, 100,600,100,600,100)

 In Field 4 (Commission), enter the following function:

=Salary*(CHOOSE(Employee Number,.5,.10,.7,.10,.5))+Salary



When an employee enters his or her number, the sheet will automatically provide the employee name, salary, and commission.

Additional query words: 3.00 3.00a 3.00b 4.00 4.00a 4.50 4.50a kbhowto calculate calculating auto lists listing listed

Keywords: kbinfo KB121020

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.