Array Formula

[Gene L.]

I am using Excel 2007 and wish to list the bin values (frequencies) for a
list of results.

I pressed "formulas" and then "frequencies" along with the cell
designations. I keep getting the message that the formula must be entered as
an "Array Formula". I cannot find out how to do this. Can you help?

Thanks very much:
Gene L.

 

[Max]

Some thoughts to get you going
(Steps are in my xl03 - I don't have xl07 - but should be similar in xl07)

"Array-enter" basically means
to press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER

Think FREQUENCY requires you to select a multi-cell range, and then
to "array-enter" the same formula into all cells in the selected range at
one go

Give this play a try ..

Let's say the data is within A2:A10
and the bins data is in B2:B4

The formula is:
=FREQUENCY(A2:A10,B2:B5)

Copy the formula above, then
Select a columnar range of 4 cells, say select A13:A16
Click inside the formula bar, paste the formula,
then press CTRL+SHIFT+ENTER to confirm the formula
Done correctly, you should see (in the formula bar)
that Excel has inserted curly braces { } around the formula, like this:
{=FREQUENCY(A2:A10,B2:B5)}
(Re-do the steps if you don't see the curlies)

The same formula will appear in all 4* cells within A13:A16
but each cell will return the different results corresponding to the bins
range
*The extra "4th" cell returns the number of values in data
that are greater than the third interval value in B4

 

[ASA]

Array formulas should be completed by pressing Control-shift-enter instead of
just enter. If you have done it right you will see a pair of braces "{ }"
around your formula.

 

[Gene L.]

I have tried to follow the instructions very carefully and a pair of braces
({ }) does appear around the formula but the results show
only a "1" in the first bin. The error message tells me that this indicates
that the formula was not entered as an array-formula. Do
you have any idea what I may be doing incorrectly?
Thanks for your help.
Gene

 

[Max]

Why not read my response as well, where I've tried to explain things to help
get you going on that function, beyond just what "array-entering" means in
general.

 

[Max]

Don't give up ..
Try this sample which shows it working:
http://www.freefilehosting.net/download/3i3j9
Frequency_demo.xls

 

[Gene L.]

Thanks for your help. I am afraid that my inexperience with Excel is causing
the problem. In the instruction
it says to "copy the formula" - copy from where? Does it mean to enter it?
if so do I enter it into a blank cell or into the
formula bar? I get the formula into the bar but when I select the range, it
disappears.
I have no problem doing this analysis in Lotus 1-2-3 but still am not able
to get anywhere with this application.
I cannot "paste in the formula bar" there is no menu to do this with the
sequence I have tried.
Well - Anyway I really do thank you for trying.
Gene

 

[Max]

Gene,
No worries, I'm as patient as they come
Let's give it one more try,
the sequence of steps is important ..

it says to "copy the formula" - copy from where? Does it mean to enter it?

Copy directly from what I posted (copy directly from where you're reading
this),
ie just select & copy the formula below:
=FREQUENCY(A2:A10,B2:B5)
(with the formula above selected, either Right-click>Copy or press CTRL+C to
copy)

Then go to Excel,
1. Select a columnar range of 4 cells, say select A13:A16
2. Click inside the formula bar, paste the formula
(either Right-click>Paste or press CTRL+V to paste)
then press CTRL+SHIFT+ENTER (CSE) to confirm the formula

If the CSE confirmation is done correctly, you should see (in the formula
bar)
that Excel has inserted curly braces { } around the formula, like this:
{=FREQUENCY(A2:A10,B2:B5)}

(Re-do the steps if you don't see the curlies in the formula bar)

 

[Gene L.]

Max:
It Works!
Thank you so much for the help (and the patience). I have printed out your
e-mails and they will come in handy.
I have to test my blood sugar several times during the day and it helps me
to know how many times the reading falls within certain levels. This is an
important tool for me.
Very best regards;
Gene L

 

[Max]

Welcome, Gene. Delighted it worked out fine for you.