Total in group of 3

P

pksinghal

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

I need urgent help in following excel sheet.

QuantityPrice
234.00
234.50
122.34
334.22
243.22

<tbody>
</tbody>

I want to get the data in new table as

3"WEIGHTED AVG PRICE OF FIRST 3 QUANTITY:
3"WEIGHTED AVG PRICE OF NEXT 3 QUANTITY.
3"WEIGHTED AVG PRICE OF NEXT 3 QUANTITIY
SO....ON

<tbody>
</tbody>



Please help and provide excel formula to do it.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
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.





StephenCrump said:
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
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-

<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))))>
Click to expand...
 
Upvote 0
pksinghal said:
Can you directly tell me formula...so that it can be done easily.
Click to expand...

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
pksinghal said:
I am putting the given formula in cell but result is not correct.
Click to expand...

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






StephenCrump said:
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.
Click to expand...
 
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
You must log in or register to reply here.

Similar threads

B
  • Question
Combinations of multiple products & quantities such total amount matchs given number
Replies
2
Views
477
Xlambda
Xlambda
Agnarr
  • Solved
Price Calculation
Replies
2
Views
271
Agnarr
Agnarr
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
344
Agnarr
Agnarr
Agnarr
  • Solved
Calculation Problem VBA
Replies
1
Views
346
Joe4
Joe4
Agnarr
  • Question
VBA code mix
Replies
1
Views
354
Joe4
Joe4

Forum statistics

Threads
1,214,840
Messages
6,121,895
Members
449,058
Latest member
Guy Boot

Share this page

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