+ Reply to Thread
Results 1 to 16 of 16

Copying formulas in a pattern

  1. #1
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Copying formulas in a pattern

    Hello, i would like to copy a sum formula and paste it at the end of each working week , which means the formula must repeat every 5 day increment. How would i do this? It would look like this:

    Total Total

    7/3/2017 Mon 10
    7/4/2017 Tue 23
    7/5/2017 Wed 0
    7/6/2017 Thu 16
    7/7/2017 Fri 22 71
    7/10/2017 Mon 8
    7/11/2017 Tue 29
    7/12/2017 Wed 3
    7/13/2017 Thu 7
    7/14/2017 Fri 23 70

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Copying formulas in a pattern

    Its better to attach your sample file with require result in respective cells.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Copying formulas in a pattern

    Here's one option assuming that your data is in columns A, B, and C starting in row 2:

    D2 =IF(B2="Fri",SUM(C$2:C2)-SUM(D$1:D1),"")

  4. #4
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Copying formulas in a pattern

    See the attached sample.
    Here's the formula that will add 5 previous values every time it's Friday.

    HTML Code: 
    You can adjust the formula based on where the data is.
    Attached Files Attached Files
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  5. #5
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Copying formulas in a pattern

    Please see attached. I want to be able to copy the sum formula in Col G at the end of each week (Friday) Thanks
    Attached Files Attached Files

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Copying formulas in a pattern

    Adjusted to the layout of your sample in post #5:

    H3 =IF(B3="Fri",SUM(G$3:G3)-SUM(H$2:H2),"")
    Last edited by 63falcondude; 04-24-2018 at 08:57 AM.

  7. #7
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Copying formulas in a pattern

    Thank you this worked . Would you mind breaking down the formula in laymans terms for me? Thanks

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: Copying formulas in a pattern

    Or try:

    =IF(B3="Fri",SUM(OFFSET(G3,-4,,5,1)),"")
    Last edited by Phuocam; 04-24-2018 at 09:08 AM.

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Copying formulas in a pattern

    Sure. The first SUM function in H3 takes the sum of G$3:G3, which is 10.
    Then, when the formula is dragged down to H4, it takes the sum of G$3:G4, which is 33.
    The formula does this all the way down the column for all of the rows where column B is "Fri".

    Since we only want the sum for one week at a time, the total of the second SUM function is subtracted from the total of the first SUM function.

    For example, in H17, we have =IF(B17="Fri",SUM(G$3:G17)-SUM(H$2:H16),"")
    SUM(G$3:G17)-SUM(H$2:H16) = 226-141 = 85
    The 141 comes from 71+70.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Copying formulas in a pattern

    Try

    in H7

    =IF($B7="Fri",SUM($G7:INDEX($G3:$G7,1)),"")

    copy down

  11. #11
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Copying formulas in a pattern

    Actually, after looking at this a little further (inspired by the formula from post #10), this will work too:

    H7 =IF(B7="Fri",SUM(G3:G7),"")

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Copying formulas in a pattern

    @63flacondude,

    I just like to make things complicated!!! Good one!

  13. #13
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Copying formulas in a pattern

    Haha thanks John.

  14. #14
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Copying formulas in a pattern

    Thank you John. It sure is easy to follow

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Copying formulas in a pattern

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

  16. #16
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Copying formulas in a pattern

    Thank you to everyone for their contribution. Appreciate all your responses. Really helpful forum and a great service to Excel users.

+ 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] Copying formulas using a pattern
    By Bradleypike in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-14-2017, 04:13 AM
  2. Pattern Copying Issue
    By Sharkawy in forum Excel General
    Replies: 18
    Last Post: 10-24-2016, 04:13 AM
  3. [SOLVED] Copying a formula with a pattern
    By jaydotjaymill in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2015, 04:20 PM
  4. [SOLVED] Copying a formula with that follows a pattern
    By Animosity in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-22-2013, 12:19 PM
  5. copying pattern from one column to another
    By chasinthetrane in forum Excel General
    Replies: 2
    Last Post: 12-28-2007, 11:57 AM
  6. copying formula pattern
    By marvinks in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-12-2007, 12:29 PM
  7. [SOLVED] Copying font and pattern dynamically
    By RTP in forum Excel General
    Replies: 1
    Last Post: 06-23-2005, 07:05 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