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).

[TABLE="width: 220"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]6,870[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]2120[/TD]
[TD="align: right"]0.309[/TD]
[/TR]
[TR]
[TD="align: right"]1540[/TD]
[TD="align: right"]0.224[/TD]
[/TR]
[TR]
[TD="align: right"]1130[/TD]
[TD="align: right"]0.164[/TD]
[/TR]
[TR]
[TD="align: right"]860[/TD]
[TD="align: right"]0.125[/TD]
[/TR]
[TR]
[TD="align: right"]1110[/TD]
[TD="align: right"]0.162[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]0.016[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]31,980[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]11660[/TD]
[TD="align: right"]0.365[/TD]
[/TR]
[TR]
[TD="align: right"]7580[/TD]
[TD="align: right"]0.237[/TD]
[/TR]
[TR]
[TD="align: right"]5030[/TD]
[TD="align: right"]0.157[/TD]
[/TR]
[TR]
[TD="align: right"]3130[/TD]
[TD="align: right"]0.098[/TD]
[/TR]
[TR]
[TD="align: right"]3830[/TD]
[TD="align: right"]0.120[/TD]
[/TR]
[TR]
[TD="align: right"]750[/TD]
[TD="align: right"]0.023[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]25,520[/TD]
[TD="align: right"]0.000[/TD]
[/TR]
[TR]
[TD="align: right"]7750[/TD]
[TD="align: right"]0.304[/TD]
[/TR]
[TR]
[TD="align: right"]6010[/TD]
[TD="align: right"]0.236[/TD]
[/TR]
[TR]
[TD="align: right"]4250[/TD]
[TD="align: right"]0.167[/TD]
[/TR]
[TR]
[TD="align: right"]2790[/TD]
[TD="align: right"]0.109[/TD]
[/TR]
[TR]
[TD="align: right"]3850[/TD]
[TD="align: right"]0.151[/TD]
[/TR]
[TR]
[TD="align: right"]870[/TD]
[TD="align: right"]0.034[/TD]
[/TR]
</tbody>[/TABLE]

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.