+ Reply to Thread
Results 1 to 7 of 7

Autofill referencing every nth cell

  1. #1
    Registered User
    Join Date
    10-26-2014
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    8

    Autofill referencing every nth cell

    Hi,

    I am having trouble using autofill to reference every nth cell.

    For example, for N=2, I want
    Please Login or Register  to view this content.
    For N=5, I want
    Please Login or Register  to view this content.
    The values are then summed and multiplied by a constant and then N.
    For N=2, SUM(column)*0.035/3*2
    For N=5, SUM(column)*0.035/3*5

    Attached is the .xlsx of a small representative data of what I am trying to achieve.

    N.B.: I have asked a similar question and was answered by user Glenn Kennedy prior in a thread called "Autofill referencing non-adjacent cells" (unable to post URL link). He used an array formula which may be applicable to this case. I have been unsuccessful in modifying the formula for this new scenario although it may speed up in reaching a solution to my problem. Once again, appreciate any help given!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-15-2015
    Location
    beirut
    MS-Off Ver
    2010
    Posts
    107

    Re: Autofill referencing every nth cell

    Try this formula in C3
    drag to right an down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Autofill referencing every nth cell

    Please try at I2
    =IF(MOD(ROWS(I$3:I3)-1,I$2),"",$B2+INDEX($B3:$B24,I$2)+($B2+INDEX($B3:$B24,I$2))^0.5)
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-26-2014
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    8

    Re: Autofill referencing every nth cell

    Quote Originally Posted by salim ali View Post
    Try this formula in C3
    drag to right an down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hi Salim,

    Thank you for the help. Dragging does produce the correct values.
    It is possible to now use the SUM function to add those values up?
    For example, for N=5, adding every 5th value in column G. I have highlighted this in orange.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-26-2014
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    8

    Re: Autofill referencing every nth cell

    Quote Originally Posted by Bo_Ry View Post
    Please try at I2
    =IF(MOD(ROWS(I$3:I3)-1,I$2),"",$B2+INDEX($B3:$B24,I$2)+($B2+INDEX($B3:$B24,I$2))^0.5)
    Hi Bo_Ry, appreciate your input. I am afraid I do not understand how your formula works but it does produce the correct values!
    May I please ask how I can modify your formula for N=3 or N=10 for example?

    Thank you.

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Autofill referencing every nth cell

    Change Value at I2:K2

  7. #7
    Registered User
    Join Date
    10-26-2014
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    8

    Re: Autofill referencing every nth cell

    Hi everyone,

    Just wanted to say I was able to solve my issue. I used the function SUMPRODUCT to add up the values every nth row for those who might be wondering.

    Thank you to all who replied, much appreciated for the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Autofill referencing non-adjacent cells
    By kandywrks in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-14-2019, 01:39 AM
  2. Autofill Range Referencing Separate Workbook
    By joshuarobbins in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-28-2016, 03:01 PM
  3. Referencing Cells to correspond with their rows for autofill
    By NINmh in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 03-12-2015, 06:33 PM
  4. [SOLVED] Autofill referencing a different workbook
    By Jbraviator in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-04-2015, 06:21 PM
  5. [SOLVED] Formula AutoFill - Last Row with Abosolute Referencing
    By JimmyG. in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-17-2014, 05:35 PM
  6. Excel 2007 : data referencing and autofill
    By firstdecimal in forum Excel General
    Replies: 0
    Last Post: 07-15-2011, 07:22 PM
  7. Trying to autofill function while referencing a workbook
    By mweiss1215 in forum Excel General
    Replies: 1
    Last Post: 07-14-2011, 12:23 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1