Creating Frequencies

[Nicrutio]

Hi,

I'm new here and hopeful this posting follows all guidelines.

I tried to search for this but was unsure what I was looking for.

I have spreadsheet that is thousands of rows long. Every 7 rows contains: a total; and 6 rows of data that make up that total.

I would like to add a column and create some formula that easily calculates what the frequency is for all those rows for every seven rows (see below).

6,870	0.00
2120	0.309
1540	0.224
1130	0.164
860	0.125
1110	0.162
110	0.016
31,980	0.00
11660	0.365
7580	0.237
5030	0.157
3130	0.098
3830	0.120
750	0.023
25,520	0.000
7750	0.304
6010	0.236
4250	0.167
2790	0.109
3850	0.151
870	0.034

<colgroup><col span="2"></colgroup><tbody>
</tbody>

Thanks for any help.

 

[wideboydixon]

Book1
	A	B
1	6,870	1.000
2	2120	0.309
3	1540	0.224
4	1130	0.164
5	860	0.125
6	1110	0.162
7	110	0.016
8	31,980	1.000
9	11660	0.365
10	7580	0.237
11	5030	0.157
12	3130	0.098
13	3830	0.120
14	750	0.023
15	25,520	1.000
16	7750	0.304
17	6010	0.236
18	4250	0.167
19	2790	0.109
20	3850	0.151
21	870	0.034
Sheet1

Cell Formulas
Range	Formula
B1	=$A1/OFFSET($A1,-MOD(ROW($A1)-ROW($A$1),7),0)

If you're starting on a row other than 1 then you'll need to adjust as appropriate. Note that the total rows show as 1 and not 0; formula could be adjusted on that but it would make it longer.

WBD

 

[Nicrutio]

WBD,

Thank you. This works.

I was messing with offset but couldn't get it right.

I appreciate it.

N.