+ Reply to Thread
Results 1 to 7 of 7

Increment cell address in a formula every 4 rows

  1. 03-01-2018, 01:44 PM #1
    champakamagalpok
    champakamagalpok is offline
    Registered User
    Join Date
    03-01-2018
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    4

    Question Increment cell address in a formula every 4 rows

    Hello excel masters!

    Sorry newbie here. I need help please:
    excel.JPG

    In my excel, I just need to increment the cell rows in the formula every 4 rows.
    Example, in Cell C4, my formula is: =C3+($F$4-$F$3)/4,
    in C5: =C4+($F$4-$F$3)/4
    C6: =C5+($F$4-$F$3)/4
    C7: =C6+($F$4-$F$3)/4
    Then in Cell C8, my formula should be: C7+($F$5-$F$4)/4,
    C9: =C8+($F$5-$F$4)/4
    C10: =C9:+($F$5-$F$4)/4
    C11: =C10+($F$5-$F$4)/4
    C12: =C11+($F$6-$F$5)/4 ...
    and this just needs to be repeated til the end of the data. I'm currently manually changing the numbers in the parenthesis (e.g. into $F$6-$F$5, $F$7-$F$6,...) every 4 rows. But I have a thousand rows in the data. I need instructions to tell excel that every after 4 rows, it should automatically increment the row numbers with the dollar sign inside the parenthesis. I'm just a regular excel user. Thanks in advance for your help!
    Reply With QuoteRegister To Reply
  2. 03-01-2018, 01:58 PM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Increment cell address in a formula every 4 rows

    Highlight cells C4:C7

    Go to Find and Replace. Find F$ and Replace All with F.

    Copy C4:C7

    Paste from C8 to the end of your data
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 03-02-2018, 01:33 AM #3
    champakamagalpok
    champakamagalpok is offline
    Registered User
    Join Date
    03-01-2018
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    4

    Re: Increment cell address in a formula every 4 rows

    Hi Jeff,

    Thanks for the reply. I've done exactly as your instruction. But when I copied C4:C7 and pasted to C8, instead of having ($F5-$F4)/4 it skipped to: ($F8-$F7)/4. Pasting to C12, it skipped again to ($F12-$F11)/4. I t increments by 4 rows instead of just 1. I wonder how I can tweak it to get my desired output. Thanks!

    Regarding your tip on attaching the file, yes I appreciate that but somehow I can't find the button to attach. There's only Insert Image.
    Last edited by champakamagalpok; 03-02-2018 at 01:35 AM.
    Reply With QuoteRegister To Reply
  4. 03-02-2018, 01:53 AM #4
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Increment cell address in a formula every 4 rows

    C4=C3+(INDEX(F:F,3+CEILING(ROWS(C$4:C4)/4,1))-INDEX(F:F,2+CEILING(ROWS(C$4:C4)/4,1)))/4
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  5. 03-02-2018, 06:16 AM #5
    champakamagalpok
    champakamagalpok is offline
    Registered User
    Join Date
    03-01-2018
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    4

    Re: Increment cell address in a formula every 4 rows

    Wow! Samba,

    It worked! This just blew my mind. Could you please explain what happens? Because I still have other data with different intervals for interpolation. Like every 2, every 5, etc. So i could apply this method for them.

    Thanks much!
    Reply With QuoteRegister To Reply
  6. 03-02-2018, 06:35 AM #6
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Increment cell address in a formula every 4 rows

    Here CEILING function did the trick
    IN
    C4 = 3+CEILING(ROWS(C$4:C4)/4,1) When you copy the formula towards down
    ROWS(C$4:C4) will be ROWS(C$4:C5),ROWS(C$4:C6),ROWS(C$4:C7),ROWS(C$4:C8),ROWS(C$4:C9) and so on
    means 1,2,3,4,5,6 ... when you divided it by 4 it will gives 0.25, 0.5, 0.75, 1, 1.25, 1.5 ..........
    the ceiling function produce the following result for this ceiling (0.25,1)=1, ceiling (0.5,1)=1, ceiling (0.75,1)=1,ceiling (1,1)=1,ceiling (1.25,1)=2,ceiling (1.5,1)=2 ... means it produces integer value in multiples of 1, for more details learn ceiling function.
    hence it will give for 1st 4 rows 1 next 4 rows 2, next 4 , 3 etc...
    Reply With QuoteRegister To Reply
  7. 03-02-2018, 09:33 PM #7
    champakamagalpok
    champakamagalpok is offline
    Registered User
    Join Date
    03-01-2018
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    4

    Re: Increment cell address in a formula every 4 rows

    Thank you for the explanation, Samba.

    It seems I still have a lot to learn with these formulas. I'll also try learning about the INDEX function.
    Thank you for your time!. Though I might leave a reply again here if ever I can't figure out how to apply this to my other data. Sorry, just a noob here.

    Best regards,
    Gerome
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Increment Cell Address every other Column (Currently skips)
    By jtnb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-03-2017, 03:53 PM
  2. Increment column address while copying formula down
    By needhelp93 in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 05-05-2014, 03:12 PM
  3. How to Copy and Paste formula with increment of the cell address value
    By purluesky in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-29-2013, 09:33 PM
  4. [SOLVED] Copy Cell Reference formula to 3 rows down but increment formula by only 1 row
    By jwnickman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-23-2013, 01:57 PM
  5. Increment Cell and address formula
    By matrix_xrs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2013, 02:53 PM
  6. how to make cell address reference increment?
    By jacko in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 03:05 AM
  7. how to make cell address reference increment?
    By jacko in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1