Add incremental value every x columns
Results 1 to 9 of 9

Thread: Add incremental value every x columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add incremental value every x columns

    This feels elementary, but I figure this is the best place to ask. My starting value is 1000 and I would like to add 500 every x columns (dynamic based on cell input). Can someone help me write the formula for this request?

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to add incremental value every x columns

    Hi, welcome to the board.

    Let's make some assumptions.
    1) Your starting value of 1,000 is held in cell A2.
    2) You want to populate the remainder of row 2, i.e. cells B2, C2, and so on.
    3) The cell input to determine "...every x columns..." is held in cell A1.
    4) For any entry where you are NOT adding an extra 500, you want to simply repeat whatever the previous value was.
    5) The value of 500 is stored in cell C1.

    Put this in B2 and copy across row 2 as far as required.

    Code:
    =A2+(MOD(COLUMN()-1,$A1)=0)*$B1
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to add incremental value every x columns

    Thank you for the quick response, Gerald. What if the date range that I am following does begin in column A. Rather I have the following:
    Starting value cell C5 (1000)
    Incremental value cell C6 (500)
    Cell Input "every x columns" C7 (3)

    If my first cell (F16) I would like to see 1000, G16 equals 1000, H16 equals 1000, then I16 equals 1500. Does that make sense?

  4. #4
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to add incremental value every x columns

    Then just adapt my proposal to reflect your real position.

    Something like this
    Code:
    =C5+(MOD(COLUMN()-3,$C7)=0)*$C6
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  5. #5
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Need to add incremental value every x columns

    Hi Gerald,

    I tried your formula and it returned 1500 / 0 / 0 / 500 (F16:I16) for me - with C5 = 1000, C6 = 500, C7 = 3.

    I have re-jigged the formula and come up with this:

    Set F16 (start) = C5

    G16:
    Code:
    $C$5+IF(MOD(COLUMNS($F$16:G16)-1,$C$7)=0,((COLUMNS($F$16:G16)-1)/$C$7)*$C$6,F16-$C$5)
    Not sure if your formula is pulling correctly Gerald? I was curious by this question hence the inquisitiveness
    Last edited by tyija1995; Aug 5th, 2019 at 12:09 PM.
    √-1 2³ ∑ π
    …And it was delicious!

  6. #6
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to add incremental value every x columns

    I guess I should have been more explicit about where exactly my formula should have been located - I was putting it in D5, and copying it across to E5...F5...G5... and so on.

    For me, this works fine.

    Your solution clearly works as well.
    It has the advantage that your use of the COLUMNS function does away with having to have the columns hard coded into the formula, which is what mine does.
    But your's seems a bit more complicated than it needs to be.
    You can simplify to this
    Code:
    =F$16+(MOD(COLUMNS($F$16:G16)-1,$C$7)=0)*((COLUMNS($F$16:G16)-1)/$C$7)*$C$6
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  7. #7
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Need to add incremental value every x columns

    Ahh that makes sense now, thanks Gerald!

    I have just tried the amended formula you have wrote, starting with G16 and going out to O16 and the increments from C6 turn strange, it first goes to 1500 (I16), then 2500 (L16) and finally 4000 (O16)

    It looks as if it adds on N multiples of the 500 every N columns, instead of adding on 1 lot of 500 every N columns?
    √-1 2³ ∑ π
    …And it was delicious!

  8. #8
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to add incremental value every x columns

    You're right, my amended version was wrong, I hadn't tested it properly, sorry !

    The correct version is even simpler . . .
    Code:
    =F$16+(MOD(COLUMNS($F$16:G16)-1,$C$7)=0)*$C$6
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  9. #9
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Need to add incremental value every x columns

    That's brilliant! Knew that there would be a more efficient formula

    Thanks Gerald, have a good one.
    √-1 2³ ∑ π
    …And it was delicious!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •