How does Excel's FREQUENCY function work?

Question

With the following function:

=FREQUENCY(C2:C724,D2:D37)

The second parameter is the BIN

What I don't understand is why Excel would increment the BIN for the rest of your values. The BIN does not change! It stays the same bin. Yet when I paste the formula for all my values it does this:

=FREQUENCY(C2:C724,D2:D37)
=FREQUENCY(C2:C724,D3:D38)
=FREQUENCY(C2:C724,D4:D39)

The last column is what was generated (and this is correct but it does not make sense!!)

Etoh        bin
15.9        20        0
14.6        19        0
14.1        18        0
13.9        17        0
13.3        16        0
13.3        15        0
13.2        14        1
12.6        13        2
12.1        12        3
11.8        11        6
11.5        10        4
11.2         9        4
11           8        8
10.5         7       10
10.3         6       26
10.3         5       27
10.2         4       40
10.1         3       89
9.8          2      151
9.7          1      205
9.5          0      102
9.1         -1       17
8.9         -2        7
8.3         -3        3
8.1         -4        2
8.1         -5        0
7.9         -6        3
7.6         -7        2
7.5         -8        2
7.5         -9        1
7.5        -10        1
7.4        -11        0
7.2        -12        0
7.1        -13        1
7.0        -14        0
7.0        -15        0
6.8
6.7
6.6
6.5
6.4
6.2
6.2
6.1
6.0
5.9
5.8
5.8
5.7
5.7
5.7
5.5
5.5
5.5
5.4
5.3
5.3
5.3
5.3
5.3
5.3
5.3
5.2
5.2
5.2
5.1
5.1
5.1
5.1
5.1
5.0
5.0
5.0
5.0
5.0
4.9
4.9
4.8
4.8
4.8
4.7
4.7
4.6
4.6
4.6
4.5
4.5
4.5
4.5
4.4
4.3
4.1
4.1
4.1
4.1
4.1
4.1
4.0
4.0
4.0
4.0
4.0
3.9
3.9
3.9
3.9
3.9
3.8
3.8
3.7
3.6
3.6
3.6
3.6
3.6
3.5
3.5
3.4
3.4
3.4
3.4
3.3
3.3
3.3
3.3
3.2
3.2
3.2
3.2
3.2
3.2
3.1
3.1
3.1
3.1
3.1
3.1
3.0
3.0
3.0
3.0
3.0
3.0
3.0
3.0
3.0
3.0
3.0
2.9
2.9
2.9
2.8
2.8
2.8
2.8
2.8
2.8
2.8
2.8
2.7
2.7
2.7
2.7
2.7
2.7
2.7
2.7
2.6
2.6
2.6
2.6
2.6
2.6
2.6
2.6
2.6
2.5
2.5
2.5
2.5
2.5
2.4
2.4
2.4
2.4
2.4
2.4
2.4
2.4
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.3
2.2
2.2
2.2
2.2
2.2
2.2
2.2
2.2
2.2
2.2
2.2
2.2
2.1
2.1
2.1
2.1
2.1
2.1
2.1
2.1
2.1
2.1
2.1
2.1
2.1
2.0
2.0
2.0
2.0
2.0
2.0
2.0
2.0
2.0
2.0
2.0
2.0
2.0
2.0
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.8
1.8
1.8
1.8
1.8
1.8
1.8
1.8
1.8
1.8
1.8
1.8
1.8
1.8
1.8
1.7
1.7
1.7
1.7
1.7
1.7
1.7
1.6
1.6
1.6
1.6
1.6
1.6
1.6
1.6
1.6
1.6
1.6
1.6
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.5
1.4
1.4
1.4
1.4
1.4
1.4
1.4
1.4
1.4
1.4
1.4
1.4
1.4
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.3
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.2
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.1
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
1.0
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.9
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.7
0.7
0.7
0.7
0.7
0.7
0.7
0.7
0.7
0.7
0.7
0.7
0.7
0.7
0.7
0.7
0.6
0.6
0.6
0.6
0.6
0.6
0.6
0.6
0.6
0.6
0.6
0.6
0.6
0.6
0.6
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.4
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.3
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.2
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
-0.1
-0.1
-0.1
-0.1
-0.1
-0.1
-0.1
-0.1
-0.1
-0.1
-0.1
-0.1
-0.1
-0.1
-0.2
-0.2
-0.2
-0.2
-0.2
-0.2
-0.2
-0.2
-0.2
-0.2
-0.2
-0.3
-0.3
-0.3
-0.3
-0.3
-0.3
-0.3
-0.3
-0.3
-0.3
-0.4
-0.4
-0.4
-0.4
-0.4
-0.4
-0.4
-0.4
-0.4
-0.4
-0.4
-0.5
-0.5
-0.5
-0.5
-0.5
-0.5
-0.6
-0.6
-0.6
-0.6
-0.6
-0.6
-0.7
-0.7
-0.7
-0.7
-0.7
-0.7
-0.7
-0.7
-0.8
-0.8
-0.8
-0.8
-0.8
-0.8
-0.8
-0.8
-0.9
-0.9
-0.9
-0.9
-0.9
-0.9
-1.0
-1.0
-1.0
-1.0
-1.0
-1.0
-1.1
-1.1
-1.2
-1.2
-1.2
-1.3
-1.3
-1.7
-1.8
-1.9
-1.9
-2.1
-2.2
-2.4
-2.4
-2.5
-2.5
-2.6
-3.0
-3.2
-3.7
-4.6
-4.6
-6.1
-6.3
-6.3
-7.0
-7.8
-8.1
-8.5
-9.0
-10.2
-13.2

If I do as both of you suggested with the $, I am getting the WRONG results:

0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

Answer 1

Unsure what you mean but it sounds like you need to use absolute addressing, prefix the column with $

Answer 2

It looks like what you've done is tried to copy and paste the function from the first cell to each of the subsequent cells.

Because it's an array function, what you need to do is:

1. Copy the text of the first cell (from the formula bar)
2. Select all the cells you are wanting the results to appear in
3. Paste the text you just copied into the formula bar
4. Press ctrl-shift-enter

This will put the same array function into all the cells

Answer 3

You need to use absolute reference for the second argument only =FREQUENCY(C2:C$724,D2:D37)

Answer 4

It's a bit of keyboarding to make it easier, not mouse work, but do exactly this:

1. In cell E2, enter =FREQUENCY(C2:C724,D2:D37). Hit Enter.
2. You should now be on cell E3. Press on the up arrow once on your keyboard to return to E2 (and it will be 0).
3. Hold down the Shift key and press on the down arrow until you've reached cell E37. E2 through E37 will be highlighted.
4. Don't do anything else other than hit the F2 key on your keyboard now.
5. Now press and hold down Ctrl + Shift. Then, with those two held down, hit Enter.
6. Voila! In every cell between E2 and E37 you should see this {=FREQUENCY(C2:C724,D2:D37)} in the formula bar (notice the {} brackets) and the formula works. This is what makes an array formula.