Excel formula (Word 2011)

[Missedmytoe]

Can anyone please help? I want to divide the number in one cell (an amount) by a total number of people. The exact purpose is to work out how much each person needs to contribute toward a set cost. At the moment, more people are opting in, so the total number of people is not set.

As an example. The cost is £300. Presently, 8 people are interested.
The cost is in cell C2. The list of people is currently in cells A2:A12

I want to divide C2 by the amount of people captured in A2:A12. This is the per person cost, that I'll need to to copy beside each name.

I cannot figure out how to write the formula to do this, so any help would be greatly appreciated.

=c2/8

Or do you want the number to change automatically if more people join your list?

= C2 / COUNTA(A2:A12)

Should work.

Thanks both! xyz Yes, the amount of people is likely to change, but the formula Jennifer gave works and accommodates this.

Am not in front on my pc but I think, if you do A:A instead of A2:A12, it’ll take into account any additional names that get added to the list, not just those in A2:A12

I was thinking this but the problem is it'll also count whatever's in A1 as one of the people. OP could get rid of the header in A1 though and have a name there instead

I would put the total of people in another cell, so that it updates every time the OP adds someone. So then the cost per person is =B3/B4, where B3 is the cost and B4 is the number of people.

OP, if you add people in the middle of the list instead of at the end, they'll automatically be counted in the total, and you don't have to change the range.

Or do A2:A102, assuming there won’t be more than 100 names

That's what I would do but you need to be sure that no one will put anything else in column A below the list. Depends on who will be using the ss and their level of comfort with Excel.

Or A:A-1

I'm not anticipating more than around 15 people and have set that range of cells as per the suggested formula.
Good tip re adding any extras to the middle. Hopefully I won't have to.

Assuming you have a header on the column that will get counted

=c2/((counta(a:a)-1)

will work forever

if you need to repeat in column or any other column
make this adjustment before you copy to the right
=c2/((counta($a:$a)-1)

then when you copy over it will automatically adjust such as
=d2/((counta($a:$a)-1)