+ Reply to Thread
Results 1 to 6 of 6

Repeating sum formula every fourth block of rows

  1. #1
    Registered User
    Join Date
    04-26-2013
    Location
    Excel
    MS-Off Ver
    Excel 2007
    Posts
    2

    Repeating sum formula every fourth block of rows

    Excel Gurus-
    I am trying to create a macro/formula that would allow me to sum a column's value for four rows and give me a ratio. I need this repeated every row with the denominator being locked every four rows (sum). After the fourth row, the denominator changes +4 for the next four rows, and so forth. Example attached and below. Noticed the last two columns.

    I am trying to get a distribution of population for each zip code. Zip codes are always shown in 4 rows each. Every zip code has four rows. SO, i am trying to take the current rows value for "New Patient" and divide that by the sum of all patients for that zip code. Repeat etc. I showed you the formula I am using, but need the denominator to stay the same for every four rows, then +4 each on the 5th row, +4 each on the 9th row etc.

    I hope this is clear...i am trying to create a pattern the formula will show this below. Check the attachment for a cleaner look

    thanks!

    Member Zip Member Age Gender New Patients AllPatients Zip dist FORMULA BEHIND SCENE
    48001 45-65 M 331 750 0.29774 =E2/SUM($E$2:$E$5)
    48001 18-44 F 37 547 0.21715 =E3/SUM($E$2:$E$5)
    48001 45-65 F 267 832 0.33029 =E4/SUM($E$2:$E$5)
    48001 18-44 M 38 390 0.15482 =E5/SUM($E$2:$E$5)
    48002 18-44 F 13 193 0.23565 =E6/SUM($E$6:$E$9)
    48002 45-65 F 74 230 0.28083 =E7/SUM($E$6:$E$9)
    48002 18-44 M 28 168 0.20513 =E8/SUM($E$6:$E$9)
    48002 45-65 M 101 228 0.27839 =E9/SUM($E$6:$E$9)
    48003 18-44 F 26 379
    48003 18-44 M 31 309
    48003 45-65 M 160 386
    48003 45-65 F 131 430
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Repeating sum formula every fourth block of rows

    In E2:

    =E3/SUMIF(A:A,A3,E:E)

    Copy down. The SUMIF totals Col E when Col A has the same zip code as stated in that row.
    Say thanks, click *

  3. #3
    Registered User
    Join Date
    04-26-2013
    Location
    Excel
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Repeating sum formula every fourth block of rows

    THANKS!!!!
    The formula was a bit off but I adjusted for it to work. You rock!

    =E2/SUMIF(A:A,A2,E:E)

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,114

    Re: Repeating sum formula every fourth block of rows

    Hi and welcome to the forum

    Try this, copied down...

    =E2/SUM(INDIRECT("E"&(ROW()-2-MOD(ROW()-2,4))+2&":E"&(ROW()-2-MOD(ROW()-2,4))+5))

    It assume your data starts on row 2...adjust the 2 and 5 accordingly if it doesnt

    edit: well duh, I didnt see that the "4" was based on column A - great catch, Harribone
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Repeating sum formula every fourth block of rows

    Like your solution though! Ain't a clue how it works mind, I think its time I expand my knowlegde some more.

    Sorry sjshah82 I told you right formula but also told you wrong cell to put it in. Oops!

  6. #6
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Repeating sum formula every fourth block of rows

    try this
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1