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
Bookmarks