1

In Excel, I need to linearly divide 'X' number of cells from value 'A' to value 'B' in 'i' step value. Ideally, I want to achieve the same result as if I was using the linspace function in Matlab.

SecretAgentMan
  • 2,856
  • 7
  • 21
  • 41
Santiago Restrepo Serna
  • 81
  • 1
  • 7
  • 1
    If you have Excel 365, you can use [`SEQUENCE`](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90) – chris neilsen Nov 08 '20 at 22:53

4 Answers4

3

this formula works similar:

=SEQUENCE(rows,columns,start,step)

rows or columns - number of repetition start - the start value

example: =SEQUENCE(,3,0,2) for 3 columns gives you: 0 2 4 if you want to fill the rows just put the number on the rows.

I hope this helps!

Ike
  • 9,580
  • 4
  • 13
  • 29
Thiago Faggioli
  • 31
  • 3
1

Combining Thiago's and ttarchala's answers, we can create a simple LINSPACE function that just wraps SEQUENCE and put it in the Name Manager for later use:

Putting the function in the name manager (PNG)

You can then use it just like any other Excel function:

Using the new LINSPACE function (PNG)

This function will produce a column vector by default (which could be TRANSPOSEd):

=LAMBDA(start, stop, num, SEQUENCE(num, 1, start, (stop - start)/(num - 1)))

Or if you prefer, a row vector:

=LAMBDA(start, stop, num, SEQUENCE(1, num, start, (stop - start)/(num - 1)))
otaithleigh
  • 11
  • 1
  • 2
  • And as a one-liner for both: `=LAMBDA(start,stop,num,trans, SEQUENCE((num-1)*trans+1, (num-1)*ABS(trans-1)+1, start, (stop - start)/(num - 1)))` trans can be 0 or 1 – The-Duck Apr 06 '23 at 20:35
0

There is no such built-in function, but you can make a named user-defined function to do it with precisely the same argument scheme using the new LAMBDA and MAKEARRAY functions:

lambda(A, B, i , makearray(1, i, lambda(r, c, A+(c-1)*(B-A)/(i-1))))

enter image description here

ttarchala
  • 4,277
  • 2
  • 26
  • 36
-1

OP references linspace (afaik Matlab/Numpy linspace works the same way), so SEQUENCE is not the answer. SEQUENCE takes these arguments,

  • number of elements (rows,columns),
  • start value,
  • increment. That is akin to arange().

linspace() takes these arguments:

  • start value,
  • end value,
  • number of elements. For example, linspace(3,5,5) would return [3, 3.5, 4, 4.5, 5]

I haven't found a formulaic way of doing that as I was in a hurry, but I did find a quick and dirty way of creating a range of cells giving a linspace type result. The instructions below are given on the basis that the start, stop, and number of elements are known values, and the range is a left-to-right, single row.

  1. Choose a cell which will be your start (leftmost) value and enter the value.
  2. Move n cells to the right (where n = the number of elements required) and enter the end value.
  3. Select the cells from start value across to end value, including the blank cells between them.
  4. On the Home tab, within the "Editing" ribbon-group, click "Fill", and select "Series..."
  5. In the series pop-up window, you only need to tick the "Trend" checkbox, then click the "OK" button.

To get the result in the example above, you'd enter "3" into A1, and "5" into E1, then select cells A1:E1.

Crexis
  • 1