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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]No 1 [/TD]
[TD]17.50[/TD]
[TD]6.83[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]No 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]No 3[/TD]
[TD]16.00[/TD]
[TD]6.83[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]No 4[/TD]
[TD]10.00[/TD]
[TD]6.83[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]No 5[/TD]
[TD]3.50[/TD]
[TD]6.83
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]No 6[/TD]
[TD]55.00[/TD]
[TD]6.84[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]No 7[/TD]
[TD]10.00[/TD]
[TD]6.84[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD="align: right"]Other Charges[/TD]
[TD]41
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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...