Dynamicaly sized array from single cell values

**greenleaf9** · 12-09-2009, 05:57 PM

I want to make an dynamic array of evenly spaced values. A Max and a Min is given and stored in two cells. And another value for number of intervals.
So for example, the following is given:
Min=25
Max=100
N=3

and I need an output of a Nx1 array with the values:
25
50
75
100

So far, I've been implementing this with {=Min+{0;1;2;3}*Interval}
Where Interval = (Max-Min)/N

But problem is I need to be able to change N and have the array change to {=Min+{0;1;2;3;...;N}*Interval}.

So the question is, how do I generate an Nx1 array?

**teylyn** · 12-09-2009, 06:40 PM

Hi,

how many cells would the array span at most? Highlight all these cells and then enter

{=IF(min+(ROW()-1)*interval>max,0,min+(ROW()-1)*interval)}

confirmed with CSE

with this formula the array starts in row 1. If you start the array in another row, adjust it so:, for example array starts in row 5

{=IF(min+(ROW()-5)*interval>max,0,min+(ROW()-5)*interval)}

You can format the array with custom format

0;;;

to suppress unwanted zeros in the unused array cells.

Would that work for you?