Formulas in Worksheets
Save Time, Reduce Errors, Expand Your Data's Potential
Entering values into your worksheet is often just the first step when preparing your data for analysis. Perhaps you need to convert your measurements from inches to centimeters. Maybe you know your process data needs to be transformed using a trigonometric function. Or you may want to regularly track average sales by region, making sure the average is always current as sales data change. Repeating these calculations every time you enter new data can be messy and laborious, and increase your risk of error.
Minitab's Formulas in Worksheets provides a simple way to put your worksheet calculations on autopilot and ensure that they're performed instantly and accurately. By assigning formulas to columns in the worksheet, you can:
- Save time when calculating and updating data
- Reduce data entry errors
- Generate new data using text, date/time, mathematical, statistical, and logical functions
Automate calculations
A Black Belt wants to transform his data before performing a statistical analysis. To save time and avoid having to recalculate values whenever he enters new data, he assigns a customized formula.
A formula assigned to column C2 calculates C2 = 2 arcsin √C1 Each time he enters data in C1, they are instantaneously transformed in C2. |  |
Eliminate mistakes
A finance department wants to reduce data entry errors by employees as they track the payment of invoices. Even simple calculations, such as date due and days past due, often result in mistakes.
To minimize the potential for error, they assign two formulas to the worksheet: The formula in Date Due adds 30 working days to each value in Date Sent. The formula in Days Past Due subtracts Date Received from Date Due when a payment is late, and records 0 when a payment is on time.
The employee needs to enter only Date Sent and Date Received. The other values are recorded automatically—without a single miscalculation or typo—every time. |  |
Generate new data
A formula can expand your options for analyzing data. For example, a quality engineer wants to better understand general patterns in her data by grouping it into categories. She uses a formula to classify the defect counts by frequency.
A formula in Category returns a text value of "low" when the number of defects is less than 2, "medium" when greater than 1 and less than 6, and "high" when greater than 5. When she enters the raw data values in Defects, they're instantly classified in the adjacent column. |  |
Now the engineer can create a bar chart using the calculated values from the formula. |  |
How to Assign Formulas in Minitab
Assigning a formula in the worksheet is simply a matter of defining a function with the Calculator and attaching it to a column.
Suppose a sales manager tracks monthly sales of computers and wants to automatically calculate cumulative sales for the year. To avoid having to recalculate the sum whenever she adds new data, the manager assigns a formula to the column.
Defining the formula
- Open a worksheet and name three columns, as shown at right.
- Type values in the Month column.
- Click a cell in the Total column. Choose Editor > Formulas > Assign Formula to Column.
|  |
- In Functions, highlight Partial sum, then click Select.
- Double-click Sales to insert the value into the expression, and then click OK.
|  |
Calculating and updating data
The color-coded indicator in the column's upper right corner (
) always lets you know the status of the formula. That way, you can be sure that the formula is properly defined and its calculations are up-to-date.
- Enter numerical values in the Sales column.
The status indicator turns from red (in this case, because no data is entered) to green (valid and up-to-date). The new values for total cumulative sales are automatically calculated in the Total column. - Revise some numerical values you entered in step 1.
The new values for total cumulative sales are automatically revised in the Total column.
|  |
Putting Formulas in Worksheets to Use
Why put yourself through the time and trouble of manually updating worksheet calculations you frequently repeat? Using Minitab's Formulas in Worksheets, you can save time, reduce data entry errors, and expand the potential for analyzing your data. What's more, you can feel confident that your calculations are accurate and up-to-date. With over 100 functions available in the Minitab Calculator, you can define the formula you need to automate your most time-consuming calculations. For more information on Formulas in Worksheets and Minitab's Calculator, see Minitab Help.