Total in group of 3

pksinghal

New Member
Joined
Apr 10, 2015
Messages
38
Hello Seniors,

I need urgent help in following excel sheet.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Quantity[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]34.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]34.50[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]22.34[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]34.22[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]43.22[/TD]
[/TR]
</tbody>[/TABLE]

I want to get the data in new table as

[TABLE="width: 500"]
<tbody>[TR]
[TD]3[/TD]
[TD]"WEIGHTED AVG PRICE OF FIRST 3 QUANTITY:[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]"WEIGHTED AVG PRICE OF NEXT 3 QUANTITY.[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]"WEIGHTED AVG PRICE OF NEXT 3 QUANTITIY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SO....ON[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Please help and provide excel formula to do it.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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


Book1
ABCDEF
1QuantityPriceCumulativeGroup Size3
2234.000
3234.502Weighted Av
4122.3441 to 334.17
5334.2254 to 630.35
6243.2287 to 937.22
71010 to 12-
813 to 15-
Sheet1
</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))))>
 
Last edited:
Upvote 0
Hi,

Can you directly tell me formula...so that it can be done easily.





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

ABCDEF
Weighted Av
1 to 3
4 to 6
7 to 9
10 to 12
13 to 15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Quantity[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Price[/TD]
[TD="align: right"]Cumulative[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Group Size[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]34.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]34.50[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]22.34[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]34.17[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]34.22[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]30.35[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]43.22[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]37.22[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]-[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]-[/TD]

</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))))>
 
Upvote 0
Can you directly tell me formula...so that it can be done easily.

I assume you mean without range names?

Let's put in some more helper cells so we can eliminate all the ROWS() references.

G3: =IF(C$7>=F3,SUMPRODUCT(B$2:B$6,(C$3:C$7>F2)*(C$2:C$6 < F3)*IF(A$2:A$6 < IF(A$2:A$6-(C$3:C$7-F3) < A$2:A$6-(F2-C$2:C$6),A$2:A$6-(C$3:C$7-F3),A$2:A$6-(F2-C$2:C$6)),A$2:A$6,IF(A$2:A$6-(C$3:C$7-F3) < A$2:A$6-(F2-C$2:C$6),A$2:A$6-(C$3:C$7-F3),A$2:A$6-(F2-C$2:C$6))))/B$10,"-")


Book1
ABCDEFG
1QuantityPriceCumulativeFromTo
2234.0000
3234.5021334.17
4122.3444630.35
5334.2257937.22
6243.2281012-
7101315-
816
9
10Group Size3
Sheet1
<f3)*if(a$2:a$6 <if(a$2:a$6-(c$3:c$7-f3)="" <a$2:a$6-(f2-c$2:c$6),a$2:a$6-(c$3:c$7-f3),a$2:a$6-(f2-c$2:c$6)),a$2:a$6,if(a$2:a$6-(c$3:c$7-f3)="" <a$2:a$6-(f2-c$2:c$6),a$2:a$6-(c$3:c$7-f3),a$2:a$6-(f2-c$2:c$6))))="" b$10,"-")<f3)*if(a$2:a$6<if(a$2:a$6-(c$3:c$7-f3)<a$2:a$6-(f2-c$2:c$6),a$2:a$6-(c$3:c$7-f3),a$2:a$6-(f2-c$2:c$6)),a$2:a$6,if(a$2:a$6-(c$3:c$7-f3)<a$2:a$6-(f2-c$2:c$6),a$2:a$6-(c$3:c$7-f3),a$2:a$6-(f2-c$2:c$6))))="" b$10,"-")<="" html=""></f3)*if(a$2:a$6>
 
Last edited:
Upvote 0
It would be much easier if Excel allowed us to calculate MIN and MAX by row across column vectors ... the Quantity required vector then would be simply:

=MAX(0,MIN(0,Qty,Qty+PrevCumulative-F2,Qty+F3-Cumulative))

So with a UDF we could have the much simpler formula .....

G3: =IF(C$7>=F3,SUMPRODUCT(Prices,OpByRow(2,OpByRow(1,Qty,Qty+PrevCumulative-F2,Qty+F3-Cumulative),0)/Size),"-")

where:

Code:
Function OpByRow(lOperator As Long, ColVector As Variant, ParamArray Others() As Variant) As Variant

    '1=Min, 2=Max, 3=Median
    Dim r As Long, i As Long
    Dim dTemp1() As Double
    Dim vTemp2 As Variant
    
    ReDim dTemp1(0 To UBound(Others) + 1)
    vTemp2 = ColVector
    For r = 1 To UBound(vTemp2)
        For i = 0 To UBound(Others)
            On Error Resume Next
            'Vector / scalar
            dTemp1(i) = Application.Index(Others(i), r, 1)
            dTemp1(i) = Others(i)
            On Error GoTo 0
        Next i
        dTemp1(i) = ColVector(r, 1)
        Select Case lOperator
        Case 1
            vTemp2(r, 1) = Application.min(dTemp1)
        Case 2
            vTemp2(r, 1) = Application.Max(dTemp1)
        Case 3
            vTemp2(r, 1) = Application.Median(dTemp1)
        End Select
    Next r

    OpByRow = vTemp2
    
End Function
 
Upvote 0
I am putting the given formula in cell but result is not correct.

Do you mean that ...

- You get different results to my weighted averages of 34.17, 30.35 and 37.22? Perhaps your layout is different to mine, in which case you'll need to point the formulae to your cells. I have attached my workbook to help you do this.

https://app.box.com/s/cqiy9lalx2h51gk1rfs5we4ptmf48jyp

OR

- You disagree with the results of 34.17, 30.35 and 37.22 for the data you posted? If so, please let us know the results you expect, and why.
 
Upvote 0
Hi,

Thanks and appreciate your hard work.

I checked the same in your workbook...I am getting correct result.....but it is upto certain quantity like 10....If i put more than that it is showing wrong result.

I have put my workbook in following URL. Please check.


https://app.box.com/s/i1x9hmw1of1bearc6titz0svm0o3eynz






Do you mean that ...

- You get different results to my weighted averages of 34.17, 30.35 and 37.22? Perhaps your layout is different to mine, in which case you'll need to point the formulae to your cells. I have attached my workbook to help you do this.

https://app.box.com/s/cqiy9lalx2h51gk1rfs5we4ptmf48jyp

OR

- You disagree with the results of 34.17, 30.35 and 37.22 for the data you posted? If so, please let us know the results you expect, and why.
 
Upvote 0
You have more data than me, so you just need to adjust the row references to accommodate the extra rows.

However, in further testing, I realised that I missed another constraint that needs to be included. In simple terms, we need to test:

=MAX(0,MIN(0,Qty,Size, Qty+PrevCumulative-PreviousUpperBoundary,Qty+CurrentUpperBoundary-Cumulative))

The UDF formula is only slightly unchanged:

I4: =IF(C$11>=F4,SUMPRODUCT(Prices,opByRow(2,opByRow(1,Qty,Size,Qty+PrevCumulative-F3,Qty+F4-Cumulative),0)/Size),"-")

However, the Excel formulae blow out in length to:

G4: =IF(C$11>=F4,SUMPRODUCT(B$2:B$10,(C$3:C$11>F3)*(C$2:C$10 < F4)*IF(G$1 < IF(A$2:A$10 < IF(A$2:A$10-(C$3:C$11-F4) < A$2:A$10-(F3-C$2:C$10),A$2:A$10-(C$3:C$11-F4),A$2:A$10-(F3-C$2:C$10)),A$2:A$10,IF(A$2:A$10-(C$3:C$11-F4) < A$2:A$10-(F3-C$2:C$10),A$2:A$10-(C$3:C$11-F4),A$2:A$10-(F3-C$2:C$10))),G$1,IF(A$2:A$10 < IF(A$2:A$10-(C$3:C$11-F4) < A$2:A$10-(F3-C$2:C$10),A$2:A$10-(C$3:C$11-F4),A$2:A$10-(F3-C$2:C$10)),A$2:A$10,IF(A$2:A$10-(C$3:C$11-F4) < A$2:A$10-(F3-C$2:C$10),A$2:A$10-(C$3:C$11-F4),A$2:A$10-(F3-C$2:C$10)))))/G$1,"-")

H4: =IF(C$11>=ROWS(H$4:H4)*Size,SUMPRODUCT(Prices,(Cumulative>(ROWS(H$4:H4)-1)*Size)*(PrevCumulative < ROWS(H$4:H4)*Size)*IF(Size < IF(Qty < IF(Qty-(Cumulative-ROWS(H$4:H4)*Size) < Qty-((ROWS(H$4:H4)-1)*Size-PrevCumulative),Qty-(Cumulative-ROWS(H$4:H4)*Size),Qty-((ROWS(H$4:H4)-1)*Size-PrevCumulative)),Qty,IF(Qty-(Cumulative-ROWS(H$4:H4)*Size) < Qty-((ROWS(H$4:H4)-1)*Size-PrevCumulative),Qty-(Cumulative-ROWS(H$4:H4)*Size),Qty-((ROWS(H$4:H4)-1)*Size-PrevCumulative))),Size,IF(Qty < IF(Qty-(Cumulative-ROWS(H$4:H4)*Size) < Qty-((ROWS(H$4:H4)-1)*Size-PrevCumulative),Qty-(Cumulative-ROWS(H$4:H4)*Size),Qty-((ROWS(H$4:H4)-1)*Size-PrevCumulative)),Qty,IF(Qty-(Cumulative-ROWS(H$4:H4)*Size) < Qty-((ROWS(H$4:H4)-1)*Size-PrevCumulative),Qty-(Cumulative-ROWS(H$4:H4)*Size),Qty-((ROWS(H$4:H4)-1)*Size-PrevCumulative)))))/Size,"-")


Book1
ABCDEFGHI
1QuantityPriceCumulativeGroup Size3
2234.000FromTo
3234.5020AB - range namesC - UDF
4122.3441334.1734.1734.17
5334.2254630.3530.3530.35
6243.2287937.2237.2237.22
7234.2210101237.2237.2237.22
8623.4412131523.4423.4423.44
9534.8818161823.4423.4423.44
10322.4423192134.8834.8834.88
1126222430.7330.7330.73
1225
Sheet1
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top