Logical formula for calculating until a number has been reached

[dicez]

Hi Guys,

I would like help with creating a measure for the following data.
I would like to categorize the products as i did manually for Apples - I calculated the total volume for the product and divided it by 3. Then I would like to start summing from the lowest volume of Apples to the highest and group them in a category such that 1/3 of the total volume is Low, 1/3 - Medium and 1/3 High.


[TABLE="width: 461"]
<colgroup><col span="2"><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]Volume[/TD]
[TD]Volume Category[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/3 Volume[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD="align: right"]7,333333333[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD]Cherry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]2[/TD]
[TD]Medium[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]2[/TD]
[TD]Medium[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]3[/TD]
[TD]Medium[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]3[/TD]
[TD]High[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]3[/TD]
[TD]High[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]3[/TD]
[TD]High[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Try

in C2
=IF(SUMIFS(B$2:B2,A$2:A2,A2)>VLOOKUP(A2,E$2:F$4,2,0)*2,"High",IF(SUMIFS(B$2:B2,A$2:A2,A2)>VLOOKUP(A2,E$2:F$4,2,0),"Medium","Low"))

 

[jmacleary]

Hi there. I'm sure there will be other solutions, but try this.

I assume your data starts in A1, and that the name and 1/3 volume are in columns D and E - if not just adjust as necessary.
In E2 put: =SUMIF(A:A,D2,B:B)/3 and copy down. This will produce the 1/3 values.
In C2 put: =CHOOSE(INT(SUMIF(A$1:A2,A2,B$1:B2)/VLOOKUP(A2,D:E,2,FALSE))+1,"Low","Medium","High","High") and copy down.

This does a running total of the volumes and changes depending on whether the 1/3 or 2/3 threshold is exceeded.

 

[dicez]

Try

in C2
=IF(SUMIFS(B$2:B2,A$2:A2,A2)>VLOOKUP(A2,E$2:F$4,2,0)*2,"High",IF(SUMIFS(B$2:B2,A$2:A2,A2)>VLOOKUP(A2,E$2:F$4,2,0),"Medium","Low"))

Thank you, it seems to work fine!