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

ID: Q129774

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel for the Macintosh, version 5.0
Microsoft Excel for Windows NT, versions 5.0, 5.0a

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

1. 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:

2. 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 for Windows, press
   CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press
   COMMAND+RETURN.

The returned results are as follows:

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

Additional query words: 5.00 5.00a 5.00c 7.00 7.00a 97 XL97 XL7 XL5

Keywords          : kbdta xlformula 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: January 8, 1999