count blanks below and divide

[avandever]

Date Order # Amt $
12/15/2010 A224614 $1,000.00
A224408
12/29/2010 A225433 $650.00
A225446
A225159
1/5/2011 0.11196961 $850.00
A225555
1/13/2011 A226591 $900.00
A226510
A226954

I would like to create another column and divde the Amt $ by the number of lines. For example, the first one would be 500 per line. The 2nd would read 216.67 per line, the third would be 425 per line, the 4th would read 300 per line. Thanks.

 

[Matty]

Not very elegant, but should do as you wish:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:71px;"><col style="width:74px;"><col style="width:64px;"><col style="width:87px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Date</td><td>Order #</td><td>Amt $</td><td>Amt $ Divided</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">15/12/2010</td><td>A224614</td><td style="text-align:right; ">$1,000.00</td><td style="text-align:right; ">$500.00</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>
</td><td>A224408</td><td>
</td><td style="text-align:right; ">$500.00</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; ">29/12/2010</td><td>A225433</td><td style="text-align:right; ">$650.00</td><td style="text-align:right; ">$216.67</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>
</td><td>A225446</td><td>
</td><td style="text-align:right; ">$216.67</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>
</td><td>A225159</td><td>
</td><td style="text-align:right; ">$216.67</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:right; ">05/01/2011</td><td style="text-align:right; ">0.11196961</td><td style="text-align:right; ">$850.00</td><td style="text-align:right; ">$425.00</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>
</td><td>A225555</td><td>
</td><td style="text-align:right; ">$425.00</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:right; ">13/01/2011</td><td>A226591</td><td style="text-align:right; ">$900.00</td><td style="text-align:right; ">$300.00</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>
</td><td>A226510</td><td>
</td><td style="text-align:right; ">$300.00</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td>
</td><td>A226954</td><td>
</td><td style="text-align:right; ">$300.00</td></tr></tbody></table>
Formula in D2, copied down, is:

=IF(C2="",D1,IF(ISNA(MATCH(TRUE,C3:C$11>0,0)),C2/(MATCH(2,1/(B3:B$11>0),1)+1),C2/MATCH(TRUE,C3:C$11>0,0)))

Which is an array formula and therefore needs committing with CTRL+SHIFT+ENTER.

Matty