1

I want to fill a column with the numbers from a specific range automatically.

For example, I have this range [-1;1] and I want Excel to calculate the step and fill whole the range despite the amount of cells I selected.

If I select 3 cells, it should do this:

-1
0
1

If I select 11 cells, this:

-1
-0,8
-0,6
-0,4
-0,2
0
0,2
0,4
0,6
0,8
1

etc. And it should work for any range I want. Is it possible?

1
  • It is, yes. If you want it to work with a selection, you'll use VBA. You'll have two cells where you enter the upper and lower bound of your desired numeric range, then select the range of cells you want populated, then run the macro. The macro would take the delta of the numeric range (n - m), then divide that by the count of the number of cells in the range minus one to find the increment amount, then populate each cell by the lower bound plus the increment. – Alex M May 7 '20 at 21:35
1

Place the lower limit in A1, the upper limit in A2 and the number of cells in A3. Then in B1 enter:

=IF(ROWS($1:1)>$A$3,"",$A$1+(ROWS($1:1)-1)*($A$2-$A$1)/($A$3-1))

and copy downward:

enter image description here

If you are using Excel 365, then you could use:

=$A$1+((SEQUENCE($A$3)-1)*($A$2-$A$1)/($A$3-1))

Or take Scott Craner's suggestion:

=ROUND(SEQUENCE(A3,,A1,($A$2-$A$1)/($A$3-1)),2)

If you want to manually select part of a column rather than use A3, then a macro would be required:

Sub PutValues()
    Dim N As Long

    With Selection
        N = .Count - 1
        .Formula = "=$A$1+(ROWS($1:1)-1)*($A$2-$A$1)/(" & N & ")"
        .Value = .Value
    End With
End Sub
6
  • if you are going to use SEQUENCE() you can start at 0 and not need the -1: SEQUENCE($A$3,,0) – Scott Craner May 7 '20 at 22:10
  • @ScottCraner You are quite correct! – Gary's Student May 7 '20 at 22:11
  • In fact you can do the whole in SEQUENCE: =ROUND(SEQUENCE(A3,,A1,($A$2-$A$1)/($A$3-1)),2) Need to round due to floating point. – Scott Craner May 7 '20 at 22:14
  • @ScottCraner Very cool........................you should post it. – Gary's Student May 7 '20 at 22:17
  • nah, I stole half your formula to make it work. Go ahead an use it. – Scott Craner May 7 '20 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.