Knowledge Base

XL98: FREQUENCY() Returns Zeroes When Entered as Horizontal Array

Article ID: 191279

Article Last Modified on 9/11/2002

Microsoft Excel 98 for Macintosh

This article was previously published under Q191279

SYMPTOMS

If the FREQUENCY function is entered as a horizontal array, regardless of the orientation of the data array or the bins array, the function returns zeros (0).

CAUSE

This problem occurs as a result of the way the FREQUENCY function processes data.

WORKAROUND

To work around this problem, nest the FREQUENCY function inside the TRANSPOSE function as in the following example.

Example

Type the following information into a worksheet:

      A1:  79     B1: Bins
      A2:  85     B2: 70
      A3:  78     B3: 79
      A4:  85     B4: 89
      A5:  83     B5:
      A6:  95     B6:
      A7:  88     B7:
      A8:  97     B8:

Select cells C1:F1 and type the following formula:
=TRANSPOSE(FREQUENCY(A1:A8,B2:B4))
NOTE: You must enter this formula as an array formula. To enter a formula as an array formula in Microsoft Excel press COMMAND+RETURN.

The returned results are as follows:

   C1:  0   D1:  2   E1:  4   F1:  2

Additional query words: XL98

Keywords: kbprb KB191279