Formula to divide evenly

[macroos]

Hi,

I need a formula in column C to divide the number in C9 by the count of numbers in column B.
There are 6 cells with numbers in column B, so it would be 41/6.
I only want the result to be two decimal numbers and it has to equal the number in C9.
Since 41 cannot be divided evenly, some numbers in column C will be 6.83 and the rest 6.84.
What is a formula that can do this?

Right now, I have =IF(C1<>"",C9/COUNT(C1:C7),"")
Cell in column C will be blank if column B does not have a number.

Thank you in advance.

	A	B	C
1	No 1	17.50	6.83
2	No 2
3	No 3	16.00	6.83
4	No 4	10.00	6.83
5	No 5	3.50	6.83
6	No 6	55.00	6.84
7	No 7	10.00	6.84
8
9		Other Charges	41

<tbody>
</tbody>

 

[Chrisdontm]

Try this formula in cell: C1

and copy it down to cell: C7

=IF($B1="","",IF(ROUNDDOWN($C$9/COUNT($B$1:$B$7),2)*COUNT($B$1:$B$7)=$C$9,ROUNDDOWN($C$9/COUNT($B$1:$B$7),2),IF((ROUNDDOWN($C$9/COUNT($B$1:$B$7),2)*COUNT($B$1:$B1))+ ROUNDUP($C$9/COUNT($B$1:$B$7),2)*(COUNT($B$1:$B$7)-COUNT($B$1:$B1))>=$C$9,ROUNDDOWN($C$9/COUNT($B$1:$B$7),2),ROUNDUP($C$9/COUNT($B$1:$B$7),2))))

Merry Christmas!

 

[StephenCrump]

Or you could try:

=IF(B1="","",FLOOR(C$9/COUNT(B$1:B$7),0.01)+0.01*(COUNT(B1:B$7)<=ROUND(MOD(C$9*100,COUNT(B$1:B$7)),2)))

 

[Chrisdontm]

Stephen,

Good job of condensing it...