Calculating an average based on values in column and gaps between rows

[jamesladbrooke1]

This is only my second time posting to mrexcel so please bear with me if my explanation is a little confusing (hopefully the table below will be fairly self explanatory). I basically have a table with two columns (and another two empty columns to contain formulas)- in column A are numbers representing amounts of money, and in column B are numbers which represents the number of times an individual uses a service. The individuals are grouped according to the values in column A (such as with the value in A1 (360), where there are a total of 6 rows until the next value 240, representing six unnamed people paying 360 collectively, and each using the service the number of times shown in column B). What I need are two formulas to go in each of column C and D. The first formula in column C will add up the numbers down column B between the entries in column A to work out the total number of visits per paying group of individuals, and place that value in the same row as the entry in column A. The second formula, in column D, will generate another number on the same row as the cell in column A which contains a value, which calculates the average income per visit for each grouping of individuals (calculated from the total income per grouping divided by the total number of visits between the individuals in the group). I hope this makes sense (I have given examples of the calculation to be made in columns C and D below, and left the last 2 groupings uncalculated). If anyone could help with this that would be massively appreciated.
Many thanks in advance,
Jamie



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]360[/TD]
[TD]1[/TD]
[TD]6(calculated from 1+0+2+1+2+0)[/TD]
[TD]60 (this is calculated from 360/(1+0+2+1+2+0)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]240[/TD]
[TD]1[/TD]
[TD]3 (calculated from 1+2)[/TD]
[TD]80 (this is calculated from 240/(1+2)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]1[/TD]
[TD]1 (there is only one individual, who visited once)[/TD]
[TD]100 (this is calculated by 100/1[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]1[/TD]
[TD]1 (calculated from 1+0)[/TD]
[TD]100(this is calculated by 100/(1+0)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]