row-indirect functions
 

This formula has to do with creating frequency bins (in this case, 10
bins and "Data" refers to a range):

{ = MIN(Data) + (ROW(INDIRECT("1:10"))*(MAX(Data) - MIN(Data) +1)/10) -
1}

I understand the logic of this formula, and I know that ROW-INDIRECT
portion of the formula is supposed to change (i.e. take on value
{1,2,3,...,10}) as we go down the column. However, I'm having trouble
understanding the formula beginning with ROW and ending before the
ASTERISK. Also, when I tried plugging it into excel, it always returns
the value 1 despite moving up and down the column.

Can anyone explain this to me? Thanks

row-indirect functions
 

This is an array formula, which means that you should type the
following:

= MIN(Data)+(ROW(INDIRECT("1:10"))*(MAX(Data)-MIN(Data)+1)/10)-1

but instead of pressing ENTER as normal, you should do CTRL-SHIFT-ENTER
at the same time. If you do it properly then Excel will wrap the curly
braces { } around the formula (do not type these yourself). If you need
to edit the formula, you should do C-S-E again, but you can copy and
paste the formula normally.

Hope this helps.

Pete

row-indirect functions
 

Hi Pete,

Thanks for replying. I knew it was an array formula, and had tried to
enter it with C-S-E. I suppose I should have clarified that when I
typed in the braces (only for the purpose of this post). Do you have
any insight as to why ROW(INDIRECT("1:10")) isn't functioning the way
it should?

row-indirect functions
 

Hi!

Are you selecting a 10 cell array and then entering the formula as an array?
That's how it looks like it should work.

Select the 10 cells where you want the bin values to appear. Type the
formula into the formula bar then enter it as an array.

Biff

wrote in message
ps.com...
Hi Pete,

Thanks for replying. I knew it was an array formula, and had tried to
enter it with C-S-E. I suppose I should have clarified that when I
typed in the braces (only for the purpose of this post). Do you have
any insight as to why ROW(INDIRECT("1:10")) isn't functioning the way
it should?