Explanation
Note: In Excel 365 , the new SEQUENCE function is a better and easier way to create an array of numbers. The method explained below will work in previous versions.
The core of this formula is a string that represents rows. For example, to create an array with 10 numbers, you can hard-code a string into INDIRECT like this:
=ROW(INDIRECT("1:10"))
The INDIRECT function interprets this text to mean the range 1:10 (10 rows) and the ROW function returns the row number for each row in that range inside an array.
The example shown uses a more generic version of the formula that picks up the start and end numbers from B5 and C5 respectively, so the solution looks like this:
=ROW(INDIRECT(B5&":"&C5))
=ROW(INDIRECT(1&":"&5))
=ROW(INDIRECT("1:5"))
=ROW(1:5)
={1;2;3;4;5}
The reason INDIRECT is used in the formula is to guard against worksheet changes. Without INDIRECT, inserting or deleting rows can change the range reference, for example:
=ROW(1:5)
will change to:
=ROW(1:4)
If row 1 is deleted. Because INDIRECT works with a reference constructed with text, it isn’t affected by changes on the worksheet.
Relative row numbers in a range
If you need an array that consists of the relative row numbers of a range, you can use a formula like this:
=ROW(range)-ROW(range.firstcell)+1
See this page for a full explanation.
Negative values
The ROW function won’t handle negative numbers, so you can’t mix negative numbers in for start and end . However, you can apply math operations to the array created by ROW. For example, the following formula will create this array: {-5;-4;-3;-2;-1}
=ROW(INDIRECT(1&":"&5))-6
Numbers in reverse order, n to 1
To create an array of positive numbers in descending order, from n to 1, you can use a formula like this:
=ABS(ROW(INDIRECT("1:"&n))-(n+1))
Explanation
The cube root of a number can be calculated manually with the exponentiation operator (^) or with the POWER function .
Manually with ^
The cube root of a number can be calculated manually by raising a number to the (1/3) using the exponentiation operator (^). In the example shown, the formula in C5 is:
=B5^(1/3)
Be sure to enclose 1/3 in parentheses to control the order of operations .
With the POWER function
The cube root of a number can also be calculated with the POWER function, by supplying 1/3 as the power argument. The equivalent formula is:
=POWER(B5,1/3)
