Add incremental value every x columns

[bcollard118]

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?

 

[Gerald Higgins]

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.

=A2+(MOD(COLUMN()-1,$A1)=0)*$B1

 

[bcollard118]

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?

 

[Gerald Higgins]

Re: Need to add incremental value every x columns

Then just adapt my proposal to reflect your real position.

Something like this

=C5+(MOD(COLUMN()-3,$C7)=0)*$C6

 

[tyija1995]

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:

$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

 

[Gerald Higgins]

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

=F$16+(MOD(COLUMNS($F$16:G16)-1,$C$7)=0)*((COLUMNS($F$16:G16)-1)/$C$7)*$C$6

 

[tyija1995]

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?

 

[Gerald Higgins]

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 . . .

=F$16+(MOD(COLUMNS($F$16:G16)-1,$C$7)=0)*$C$6

 

[tyija1995]

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.