Excel help. Dividing a number equally between x number of cells.

Droolinggimp

Droolinggimp

Droolinggimp

Soldato
Joined
16 May 2004
Posts
7,114
Location
Derby
Ok I have £1000 and would like evenly split it between a number of cells/list.
For example, I have ten people in a list who will receive £100 each. I want it so the £1000 can be changed to whatever amount i need and it fills in the list of people share equally.
£1000 < so when this changes the 4 cells below (the money ones) change accordingly.
dave £100
john £100
sue £100
steve £100

Get what I mean? Im not great with excel so I do need help.
 
Cell with amount you want to distribute is A1.

X is the number of people you have. You could derive this from a formula if it's going to change a lot.

In the cell next to the first person in your list type "=$A$1/X" but replace the X with the number of people.

Copy that and paste in into the cells next to the rest of your list.
 
If you have all your people in a single column, you could also use counta, to could the number it is to be divided by. For example if you had 10 names in A1:A10, =Counta(A:A) Would return 10. But if you changed the number of people in the column, counta only returns populated cells, so you could add/remove from the A column with the divisible value always being the correct number of people listed. Then use that stored value to divide from the total.

You can combine this, the column A being the list of names, C1 storing the £1000 value and for this to be put in column B alongside the names.

=SUM(C1)/COUNTA(A:A)

Maybe this ramble has made some sense...
 
Excel.png


Each of the cells B2 - B7 contains:
=(B1/SUM(IF(ISBLANK(A2),0,1),IF(ISBLANK(A3),0,1),IF(ISBLANK(A4),0,1),IF(ISBLANK(A5),0,1),IF(ISBLANK(A6),0,1),IF(ISBLANK(A7),0,1)))
 
touch said:
Excel.png


Each of the cells B2 - B7 contains:
=(B1/SUM(IF(ISBLANK(A2),0,1),IF(ISBLANK(A3),0,1),IF(ISBLANK(A4),0,1),IF(ISBLANK(A5),0,1),IF(ISBLANK(A6),0,1),IF(ISBLANK(A7),0,1)))
Click to expand...

Requires a fixed number of cells.

HecFam said:
If you have all your people in a single column, you could also use counta, to could the number it is to be divided by. For example if you had 10 names in A1:A10, =Counta(A:A) Would return 10. But if you changed the number of people in the column, counta only returns populated cells, so you could add/remove from the A column with the divisible value always being the correct number of people listed. Then use that stored value to divide from the total.

You can combine this, the column A being the list of names, C1 storing the £1000 value and for this to be put in column B alongside the names.

=SUM(C1)/COUNTA(A:A)

Maybe this ramble has made some sense...
Click to expand...

Nailed it. I didn't know COUNTA existed!
 
BTW, just be aware of absolute cell references.

The formula provided above by @HecFam is what I would use if you had a changing list of people.

However make sure you absolute the cell with the amount to be split, i.e. =SUM($C$1)/COUNTA(A:A)
 
wesimmo said:
BTW, just be aware of absolute cell references.

The formula provided above by @HecFam is what I would use if you had a changing list of people.

However make sure you absolute the cell with the amount to be split, i.e. =SUM($C$1)/COUNTA(A:A)
Click to expand...

There are 40 people. Names in column A with column B being where I want the amount filled with an even amount. I copied and pasted you formula (changed the cells to reflect mine and it juts listed 0 in all cells next to names
 
You must log in or register to reply here.
Back
Top Bottom