+ Reply to Thread
Results 1 to 42 of 42

Repeat a Formula every 10th Row

  1. #1
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Repeat a Formula every 10th Row

    Hello All. New member with fairly basic Excel knowledge. Can anyone help?
    I want to repeat a formula in every 10th row in a column.
    The formula is totaling 10 amounts in the column to it's left.
    Overall excel sheet is very long and would take ages to copy/paste all the way down every 10th row.
    Any guidance would be greatly appreciated.
    Many thanks.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,691

    Re: Repeat a Formula every 10th Row

    Welcome to the forum.

    Which version of Excel do you have? There is no 3.2 ...

    There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi AliGW. Thanks for your message. I am using Excel 2016. Will now try to attach a sample workbook. Thanks.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,691

    Re: Repeat a Formula every 10th Row

    Please update your forum profile with that information.

  5. #5
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi, Hopefully sample excel sheet attached.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi, As you will see from the Excel Sheet, it is not every 10th Row as I said Earlier, it is every 14th Row in column G. In that row, I want to total the 14 amounts to it's left in column F.
    Thanks.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    Try, row 14, copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  8. #8
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi Trevor, Thanks for the suggestion. I must be doing something wrong as that formula in row 14 does not produce an amount, it remains showing as the formula and drag/drop down the column shows the formula in every cell. Appreciate you trying to help.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    I'm guessing the cell/column is formatted as Text. Format as General and try again (re-enter the formula).

  10. #10
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    The Column was formatted as "Currency". I changed it to "General" but it still just shows the Formula in the cell. Any other ideas?

  11. #11
    Registered User
    Join Date
    06-16-2011
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Repeat a Formula every 10th Row

    Try this on Column G with after cpoing the formula.
    Make sure there is no space prior to "="

    Highlight Column G then go to

    'Data' tab --> 'Text to Columns'--> Delimineted -->Next-->Uncheck all the Delimeniters-->Generals-->Finish

  12. #12
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi Trevor. My mistake (I had a "space" before your formula). Now working a treat. You have great knowledge. I really appreciate your help. Dougie.

  13. #13
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Thanks to you too Dan4ish. You were correct, I had a space before "="

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    You're welcome. Thanks for the rep.


    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  15. #15
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi All, Used =IF(MOD(ROW(),14)=0,SUM(F1:F14),"") to provide an amount every 14 rows in a column which was helpfully provided by "Guru" last September. I am trying to use it again in another much the same spreadsheet but get a message "You've entered too many arguments for this function". Could someone advise if they can see anything wrong with the Function.
    Appreciate your help.

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    Post 7: =IF(MOD(ROW(),14)=0,SUM(F1:F14),"")
    Post 15: =IF(MOD(ROW(),14)=0,SUM(F1:F14),"")

    They look the same to me (as copied and pasted here).


    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

  17. #17
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi, Thanks for checking. Any idea which I would get the message ""You've entered too many arguments for this function". ??

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,829

    Re: Repeat a Formula every 10th Row

    Maybe you have missed a closing bracket somewhere, or put an extra comma in the formula by mistake. Copy the actual formula from your new workbook and paste it directly into a new post here, or provide a sample workbook so we could see for ourselves. (see yellow banner at top of screen)

    Hope this helps.

    Pete

  19. #19
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi Pete, Thanks for looking at this. The Formula I am using in the new workbook is =IF(MOD(ROW(),15)=0,SUM(F10:F24),"") It is being used in column G to make a sum of the amounts in F10:F24 and then repeat the formula every 15 Rows down Column G. Hope that makes sense. Dougie.

  20. #20
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    This would do it:

    =IF(MOD(ROW(,14)=0,SUM(F1:F14),"")

    Or this:

    =IF(MOD(ROW(),14=0,SUM(F1:F14),"")

  21. #21
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    =IF(MOD(ROW(),15)=0,SUM(F10:F24),"")

    Again, nothing wrong with this formula.

    Are you copying it and pasting it here or re-typing it?

  22. #22
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi, I'm retyping it. I'll try copy/pasting it. Can't see why I get that error message!!! Doug.

  23. #23
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    You are re-typing it correctly. Try copying and pasting from your post into your worksheet.

  24. #24
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi, Tried copy/pasting. Still gives me the same message "You've entered too many arguments for this function". Seems strange... any other ideas?? Doug

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,691

    Re: Repeat a Formula every 10th Row

    Attach your workbook, please - make sure it's desensitised. The formula is correct.

    EDIT: Forget that - see below.
    Last edited by AliGW; 04-13-2024 at 01:00 PM.

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,691

    Re: Repeat a Formula every 10th Row

    I've put this into M14 and dragged it down:

    =IF(MOD(ROW(),14)=0,SUM(F1:F12),"")

    You will see it working in the attachment.

  27. #27
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    Seems strange... any other ideas?? Doug
    Yes. As we keep asking/suggesting: post the workbook.

  28. #28
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    Another sample file with both formulae. No point us keep testing working formulae. We need to see the thing that's broken.
    Attached Files Attached Files

  29. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,691

    Re: Repeat a Formula every 10th Row

    @jolly$

    In your sample workbook, just remove the = from the start of the formula that is not being accepted - then we can look at it and see what the problem is.

  30. #30
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula

    Hi, I know I'm frustrating you but I cannot get this formula to work. Attached is part of the spreadsheet (with the formula in G15). Now producing a result there but not repeating down the page. I must be doing something wrong.
    Doug.
    Attached Files Attached Files

  31. #31
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    Works for me:

    Please Login or Register  to view this content.
    Last edited by TMS; 04-13-2024 at 03:13 PM.

  32. #32
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Thank you all for looking at this. I have no idea what I'm doing wrong. but it will not work for me. Appreciate you all trying to help me though. I'll just have to keep trying. Thanks. Doug

  33. #33
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,691

    Re: Repeat a Formula every 10th Row

    Have you copied the formula down by dragging?

  34. #34
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Hi Ali, Not sure how to drag the formula down to the cell. Doug.

  35. #35
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    I am a little confused. If there was an error in the formula, you wouldn't be able to save the workbook. Hence Ali's suggestion that you remove the equals sign from the formula. But the workbook has a formula in it and it works. So, confused .

    The formula works and drags down (for me).

  36. #36
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    See attached example.
    Attached Files Attached Files

  37. #37
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Me too TMS. I just don't know what is going wrong or I am doing wrong.

  38. #38
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,691

    Re: Repeat a Formula every 10th Row

    There’s a little black square bottom right of the cell - grab it and drag downwards.

  39. #39
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    Or, just select the cell and copy it. Then select all the cells below it and paste the formula.

  40. #40
    Registered User
    Join Date
    08-12-2023
    Location
    Gourock, Scotland
    MS-Off Ver
    2016
    Posts
    18

    Re: Repeat a Formula every 10th Row

    Thanks for the help. The drag worked. Problem seems to have been headings I had on the Worksheet which seemed to interfere with the formula. All sorted now and working.
    Your help is greatly appreciated. Many thanks.

  41. #41
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,746

    Re: Repeat a Formula every 10th Row

    You're welcome. Thanks for the rep.


    Not sure I understand the cause or solution but, not to worry.

  42. #42
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,691

    Re: Repeat a Formula every 10th Row

    I fail to see how the headings could have been at fault - but once again, without an engine to see, I can't tell you what's wrong under the bonnet. I think it would have been beneficial to you in the long run to get it properly diagnosed, but there we are.

    Thanks for the rep.

+ 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. Average Formula every 10th row based on another column
    By maym in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-22-2020, 05:23 PM
  2. Formula to calculate the average of 1st-10th, 11-20th and 21-end of month
    By layles1 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-04-2019, 12:33 AM
  3. Create Formula, Repeat Formula, Move to next Worksheet and repeat
    By sparkyster in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2018, 03:23 PM
  4. [SOLVED] Populating every 10th cell in column with repeat of last data
    By aglawrence in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-12-2017, 04:14 PM
  5. [SOLVED] copy formula in every 10th Row
    By Formula-seeker in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-09-2017, 04:40 PM
  6. [SOLVED] Change Reference by dragging formula every 10th time
    By lulumink in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2014, 08:55 AM
  7. Replies: 4
    Last Post: 03-08-2014, 08:01 AM

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