How to make a sequence of one number increment from one by one across a range.

[roudarreza]

Hello,

I hope everyone is doing great!

I would like to know if there is a way (function, macro, etc.) to make a sequence of one number (in this case the number is in cell B1=22.5) increment from one by one across a range (from cell A1 to A9).

Thank you for reading and your time.

	A	B
1	3	22.5
2	3
3	3
4	3
5	2.5
6	2
7	2
8	2
9	2

 

[Joe4]

Welcome to the Board!

Unfortunately, your question, in conjunction with the data you show, makes no sense to me.
Can you explain in more detail? Walk us through this example, step-by-step, explaining exactly how you get those values.

 

[roudarreza]

Thank you so much for your swiftly reply, Joe4. For more context this is related to a baseball scenario/problem:

1. Value in E3 is the total batters faced by the opposing pitcher. That number could be anything. Value in E4 is just the sum of those values (B3:D11) so I can compare that both number match.
2. What I am trying to know is if there is a way to divide such number by each plate appearance by batter (it cannot be greater than one but sometimes it could be less than one).
3. The image above shows the sequence. In baseball there is a sequence from number batter one to batter number nine which cannot be changed. Once batter nine consumes their plate appearance the count starts again.

4. The second image shows the aggregate of those plate appearances which is why I intended to obtain at the end.

Thank you so much, if anything else is needed, like a file or link to the actual doc, please let me know.

 

[Joe4]

I am a big baseball fan myself.
But how can a pitcher face a partial/fractional batter?

So, are you looking to populate a grid like in your first image (columns B-D), or like your second (aggregate in a single column)?

 

[roudarreza]

Oh, nice! I hope you are a not an A´s fan though, they are having an awful season.

A pitcher facing a partial batter has to do with an average problem. A pitcher could face 17 batters in one game, 25 in another, so in a sample of 5 games if a pitcher has faced 15, 17, 17, 22 and 25 batters the average is 19.2. But you are right I did not think about that so probably the best would be to just round to the closer number.

About you second question - and to be honest I just would like to make it work - it does not matter if it is like in the first or second image. Of course the second image would be for me more convenient but whatever works best.

TIA.

 

[Joe4]

No, I am a Blue Jays fan. It is sad what has happened to the A's. They use to be a good franchise.

Are you open to a VBA solution?
There may be a way to do it with a formula, but I am afraid it is beyond my formula prowess.

 

[roudarreza]

Oh, I see, a Blue Jays fan, nice, it is tough division to win, same for a wildcard. Anyway. Yes I am open to a VBA solution, whatever it takes. I want to reiterate my gratitude for your help.

 

[Joe4]

OK, see if this helps you. I made some minor modifications to your structure. This is what I have initially:

I put the total we want to get to in cell E1. I increased the number of columns in case it is more than that.

Then, I created this VBA code:

Sub PopulateGrid()

    Dim c As Long
    Dim r As Long
    Dim tot As Double
    
    Application.ScreenUpdating = False
    
'   Set initial column to start in (col B = 2)
    c = 2
    
'   Get total from cell E1
    tot = Range("E1").Value
    
'   Loop through columns
    Do
'       Loop through rows 3-11
        For r = 3 To 11
'           Check to see if running total > 1
            If tot > 0 Then
'               Check to see if value left>=1
                If tot > 1 Then
                    Cells(r, c).Value = 1
                Else
                    Cells(r, c).Value = tot
                    Exit Do
                End If
'           Decrement tot
            tot = tot - 1
            Else
                Exit Do
            End If
        Next r
'       Increment to next column
        c = c + 1
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

When you run it on the data grid I show above, this is what the result looks like:

Hopefully, that gives you what you are looking for. Tweaks can be made to the code, as needed.

 

[Eric W]

Here's a way to do it with a formula. Any fractional amount is ignored.

Book1
	A	B	C	D	E
1					21.85
2	a	3
3	b	3
4	c	3
5	d	2
6	e	2
7	f	2
8	g	2
9	h	2
10	i	2
Sheet3

Cell Formulas
Range	Formula
B2:B10	B2	=LET(q,INT(E1/9),q+((SEQUENCE(9)<=E1-9*q)))
Dynamic array formulas.