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

jamesladbrooke1

New Member
Joined
Apr 9, 2016
Messages
5
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



36016(calculated from 1+0+2+1+2+0)60 (this is calculated from 360/(1+0+2+1+2+0)
0
2
1
2
0
24013 (calculated from 1+2)80 (this is calculated from 240/(1+2)
2
10011 (there is only one individual, who visited once)100 (this is calculated by 100/1
10011 (calculated from 1+0)100(this is calculated by 100/(1+0)
0
3001
5
2507
0
3

<tbody>
</tbody>
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top