Control enter vs. drag down

[ammartino44]

Hello, if I select four cells, then write a formula in the first cell and hit control enter it copies the SAME formula in all the cells. However, if I do a drag down it moves the reference of the cells. Why is this? It seems like the function is the same.

[JieJenn]

Maybe because your reference isn't lock? Check the dollar $

http://blogs.office.com/2011/08/17/m...igns-in-excel/

[ammartino44]

Actually, it's only because it's an array (for frequency). That's really weird. How can how all cells be considered 1 array? I'm doing a frequency array with bins and if I try to delete one cell it gives me an error. Can I do frequency without an array. For example, just taking one bin (50)?

[JieJenn]

From my experience, frequency is an array only formula.

[MrShorty]

Yes, =FREQUENCY() is an array formula intended to return all of the results for all of the bins in one step.

If you want individual results from the FREQUENCY() function, you can nest it inside of an INDEX() function. =INDEX(FREQUENCY(data_array,bins_array),5) will return the 5th result from the FREQUENCY() function. It is just a matter of knowing what value to put into the row/column arguments for the INDEX() function.

Since FREQUENCY() is really just counting values in a "bin", you could also work out a COUNTIF() or COUNTIFS() function that performs the same task.