formulafilldown
New Member
Hi, I need help on filling a formula down with a constant increment. I would like the first cell to be '=+B1' the next to be '=+B4' the next to be '=+B7' etc... so that the increment is 3. How can this be accomplished?
fill down a formula with an increment [SOLVED]
- Thread starter formulafilldown
- Start date
Hi.
My name is Leo and i'm from Nova Friburgo, Brasil.
This is my first post here.
So, I want to understand that formula presented by Chandoo. I didn't realize what it gives as a result. I wrote the formula exactly as explained above, but I didn't understand its result. When I copy it down, it gives only 0 (zero) in all cells.
Regars,
Leo
My name is Leo and i'm from Nova Friburgo, Brasil.
This is my first post here.
So, I want to understand that formula presented by Chandoo. I didn't realize what it gives as a result. I wrote the formula exactly as explained above, but I didn't understand its result. When I copy it down, it gives only 0 (zero) in all cells.
Regars,
Leo
jmackeyiii
New Member
=(ROW(C4)*3)-2
Does not require a helper/dummy column. The equation can be improved by adding a cell reference.
=(ROW(C4)*$A$1)-($A$1-1)
Does not require a helper/dummy column. The equation can be improved by adding a cell reference.
=(ROW(C4)*$A$1)-($A$1-1)
Hi all
I have evaluated the formulae and find I am struggling to adapt this to my problem. So I am asking for some help. I have several sheets in a workbook, however I need to copy cell D64 from Calculations sheet to Fees & Charges sheet cell H11. I wish to have cell H12 incremented by 6 cells down the column D in the Calculations sheet so H12 = D70, H13=D76 so on an so forth. I have over 240 cells to fill so am looking to formula this to speed things along. I have tried various incantations with offset eg =OFFSET(Data!$A$1,ROW(A1)*22-19,0) that I found on another site but couldn't work out their 22-19 scenario into my increments and changed "Data" to Calculations etc etc.
I have evaluated the formulae and find I am struggling to adapt this to my problem. So I am asking for some help. I have several sheets in a workbook, however I need to copy cell D64 from Calculations sheet to Fees & Charges sheet cell H11. I wish to have cell H12 incremented by 6 cells down the column D in the Calculations sheet so H12 = D70, H13=D76 so on an so forth. I have over 240 cells to fill so am looking to formula this to speed things along. I have tried various incantations with offset eg =OFFSET(Data!$A$1,ROW(A1)*22-19,0) that I found on another site but couldn't work out their 22-19 scenario into my increments and changed "Data" to Calculations etc etc.
Wookiee
Member
@ jmackeyiii (or anyone else who'd like to chime in, since it looks like jmackeyiii hasn't ever posted since his intial post in this thread)
i'm trying to wrap my head around the use of the ROW function to generate a series of numbers in specific increments. although i'm not sure how i would ever come up with the idea of multiplying the current row by 3 then subtracting 2 to establish a +3 increment, i see that it works and that's enough to get me started.
my real question regards the first formula in the post.
seems like the more efficient formula to me, as it doesn't require either a helper column or reference to a helper cell. i'm curious why the second example is supposed to be an improvement, as the formula is longer and requires a helper cell.
i'm trying to wrap my head around the use of the ROW function to generate a series of numbers in specific increments. although i'm not sure how i would ever come up with the idea of multiplying the current row by 3 then subtracting 2 to establish a +3 increment, i see that it works and that's enough to get me started.
my real question regards the first formula in the post.
Code:
=(ROW(C4)*3)-2
SirJB7
Excel Rōnin
@Wookie
Hi!
Because of this:
http://chandoo.org/forums/topic/fill-down-a-formula-with-an-increment#post-150
Regards!
PS: It's very unusual that original posters come back to such old topics, usually you'd better start a new one and place a reference to the old if you consider it properly. But in this case you're asking about two specific posts of this topic, so it's ok.
Despite of this, perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).
Hi!
Because of this:
http://chandoo.org/forums/topic/fill-down-a-formula-with-an-increment#post-150
Regards!
PS: It's very unusual that original posters come back to such old topics, usually you'd better start a new one and place a reference to the old if you consider it properly. But in this case you're asking about two specific posts of this topic, so it's ok.
Despite of this, perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).
Wookiee
Member
thanks, sirjb7. i am familiar with the general forum conventions, i just somehow found this topic link from another chandoo post and was curious about the logic that jmackey was using. although i did realize the age of the original thread, i felt it most appropriate to post a reply here since it was one specific post that had me confused (well, mostly).
i think i should have just deconstructed chandoo's formula before trying to interpret the one jmackey submitted. i do have another question about that, but i'll post it as a new separate thread.
i think i should have just deconstructed chandoo's formula before trying to interpret the one jmackey submitted. i do have another question about that, but i'll post it as a new separate thread.