Microsoft KB Archive/39389

Mac Works: Array Formulas Not Sorting Correctly

PSS ID Number: Q39389 Article last modified on 10-22-1998

1.0 1.1 2.0 4.0

MACINTOSH

=
=========================================================

= SUMMARY =

In a Microsoft Works 1.x, 2.0, or 4.0 spreadsheet, functions that have cell references in an array format do not sort correctly. For example, the SUM function does not sort correctly when the function’s cell references are in the following array format:

=SUM(A1:F3)

The function sorts correctly if the reference is not in an array format, as in the following example:

=SUM(A1,B1,C1,D1,E1,F1,A2,B2,C2,D2,E2,F2,A3,B3,C3,D3,E3,F3)

= RESOLUTION =

To change the format of an array function to produce the correct sorting results, use the following steps:

Works 1.x or 2.0

 * 1) Select the first cell in the column that contains the array formula.
 * 2) Re-enter the formula and press the ENTER key.
 * 3) Select the same cell and drag down the column to select all the other cells where the formula should be present.
 * 4) From the Edit menu, choose Fill Down.

This information applies to all functions whose arguments consist of arrays, including Average, Count, Max, Min, Ssum, StDev, Sum, and Var.

Works 4.0
To change the format of an array function to produce the correct sorting results, use absolute references in your formulae, as shown below:

=SUM($A$1:$F$3) Microsoft has confirmed this to be a problem in Works 1.x, 2.0, and 4.0. This problem does not occur in Works version 3.0. We are researching this problem and will post new information here as it becomes available.

= MORE INFORMATION =

In Works 4.0, because the sort is based on relative references, you may see “N/A” in a sorted cell, as well as “OffSheet” appearing as one of the cell references in the formula.

KBCategory: kbbuglist kbfixlist KBSubcategory: macworkskb

Additional reference words: 1.00 1.10 2.00 mswksss mswksprob ss manual calculate 4.00 macworks mwksss range mwkswp

=
========================================================= Keywords : kbbuglist kbfixlist Version : 1.0 1.1 2.0 4.0 Platform : MACINTOSH Issue type : kbbug ============================================================================= Copyright Microsoft Corporation 1998.