Repeating sum formula every fourth block of rows

[sjshah82]

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

[Harribone]

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.

[sjshah82]

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

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

[FDibbins]

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

[Harribone]

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!

[scott.s.fower]

try this

Please Login or Register  to view this content.