Qty: =A2:A6
Prices: = B2:B6
Cumulative: = C3:C7
PrevCumulative: =C2:C6
Size: = F3
E4: =(ROWS(E$4:E4)-1)*Size+1 & " to " & ROWS(E$4:E4)*Size
F4:<rows(f$4:f4)*size)*if(qty<if(qty-(cumulative-rows(f$4:f4)*size)<qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative)),qty,if(qty-(cumulative-rows(f$4:f4)*size)<qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative)))) size,"-")="" array="" entered
<rows(f$4:f4)*size)*if(qty<if(qty-(cumulative-rows(f$4:f4)*size)<qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative)),qty,if(qty-(cumulative-rows(f$4:f4)*size) <="" qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative))))="" size,"-")="" array="" entered
<rows(f$4:f4)*size)*if(qty<if(qty-(cumulative-rows(f$4:f4)*size)<qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative)),qty,if(qty-(cumulative-rows(f$4:f4)*size)<qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative)))) size,"-")
=IF(C$7>=ROWS(F$4:F4)*Size,SUMPRODUCT(Prices,(Cumulative>(ROWS(F$4:F4)-1)*Size)*(PrevCumulative < ROWS(F$4:F4)*Size)*IF(Qty < IF(Qty-(Cumulative-ROWS(F$4:F4)*Size) < Qty-((ROWS(F$4:F4)-1)*Size-PrevCumulative),Qty-(Cumulative-ROWS(F$4:F4)*Size),Qty-((ROWS(F$4:F4)-1)*Size-PrevCumulative)),Qty,IF(Qty-(Cumulative-ROWS(F$4:F4)*Size) < Qty-((ROWS(F$4:F4)-1)*Size-PrevCumulative),Qty-(Cumulative-ROWS(F$4:F4)*Size),Qty-((ROWS(F$4:F4)-1)*Size-PrevCumulative))))/Size,"-") Array entered
| A | B | C | D | E | F |
---|
1 | Quantity | Price | Cumulative | | Group Size | 3 |
2 | 2 | 34.00 | 0 | | | |
3 | 2 | 34.50 | 2 | | | Weighted Av |
4 | 1 | 22.34 | 4 | | 1 to 3 | 34.17 |
5 | 3 | 34.22 | 5 | | 4 to 6 | 30.35 |
6 | 2 | 43.22 | 8 | | 7 to 9 | 37.22 |
7 | | | 10 | | 10 to 12 | - |
8 | | | | | 13 to 15 | - |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
</rows(f$4:f4)*size)*if(qty<if(qty-(cumulative-rows(f$4:f4)*size)<qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative)),qty,if(qty-(cumulative-rows(f$4:f4)*size)<qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative))))></rows(f$4:f4)*size)*if(qty<if(qty-(cumulative-rows(f$4:f4)*size)<qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative)),qty,if(qty-(cumulative-rows(f$4:f4)*size)></rows(f$4:f4)*size)*if(qty<if(qty-(cumulative-rows(f$4:f4)*size)<qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative)),qty,if(qty-(cumulative-rows(f$4:f4)*size)<qty-((rows(f$4:f4)-1)*size-prevcumulative),qty-(cumulative-rows(f$4:f4)*size),qty-((rows(f$4:f4)-1)*size-prevcumulative))))>