Top Ten Formula

[tvman5683]

I use a formula to identify the top ten items in a list of potential items sorted by a descending PRICE value. There is a quantity coulmn summing up the top ten pieces.
Sometimes an item is sold in quantitiy of 2. If it's item number 10 in the sorted list the formula will not identify but stops at the 9th item. Is there a way to "move next" similar to a VB code stepping through data until it can come up with the toal 10 pieces? here's the formula I have been using. Thanks for any help or ideas.

=SUMIF($A$1:$A2,A2,$C$1:C2)<=10

Item</SPAN>	PRICE</SPAN>	QUANTITY</SPAN>	TOP TEN </SPAN>
A</SPAN>	2.28</SPAN>	1</SPAN>	TRUE</SPAN>
B</SPAN>	2.16</SPAN>	1</SPAN>	TRUE</SPAN>
C</SPAN>	1.01</SPAN>	1</SPAN>	TRUE</SPAN>
D</SPAN>	0.96</SPAN>	1</SPAN>	TRUE</SPAN>
E</SPAN>	0.84</SPAN>	1</SPAN>	TRUE</SPAN>
F</SPAN>	0.72</SPAN>	1</SPAN>	TRUE</SPAN>
G</SPAN>	0.72</SPAN>	1</SPAN>	TRUE</SPAN>
H</SPAN>	0.72</SPAN>	1</SPAN>	TRUE</SPAN>
I</SPAN>	0.72</SPAN>	1</SPAN>	TRUE</SPAN>
J</SPAN>	0.72</SPAN>	2</SPAN>	FALSE</SPAN>
K</SPAN>	0.72</SPAN>	1</SPAN>	FALSE</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=2><COL></COLGROUP>

 

[delos001]

Hey,

Try this in the TOP 10 column:

=IF(COUNTIF($C$2:C2,">"&0)<=10,"TRUE","FALSE")

 

[tvman5683]

Thanks for the reply. Here's the result of your formula. It selected the first ten lines only(COUNT) and did not reset at the change in name in column A.
so for the first customer the sum of the trues is 13 instead of 10.

NAME</SPAN>	FCAST</SPAN>	SUM OF 10</SPAN>	TOP 10</SPAN>
SMITH</SPAN>	6.48</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	6</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	4.68</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	4.2</SPAN>	2</SPAN>	TRUE</SPAN>
SMITH</SPAN>	1.2</SPAN>	2</SPAN>	TRUE</SPAN>
SMITH</SPAN>	0.84</SPAN>	2</SPAN>	TRUE</SPAN>
SMITH</SPAN>	0.84</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	0.84</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	0.72</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	0.6</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	0.6</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.6</SPAN>	1</SPAN>	FALSE</SPAN>
JONES</SPAN>	14.28</SPAN>	1</SPAN>	FALSE</SPAN>
JONES</SPAN>	11.4</SPAN>	1</SPAN>	FALSE</SPAN>
JONES</SPAN>	11.11</SPAN>	1</SPAN>	FALSE</SPAN>
JONES</SPAN>	10.92</SPAN>	1</SPAN>	FALSE</SPAN>
JONES</SPAN>	8.64</SPAN>	1</SPAN>	FALSE</SPAN>
JONES</SPAN>	3.6</SPAN>	1</SPAN>	FALSE</SPAN>
JONES</SPAN>	3.24</SPAN>	1</SPAN>	FALSE</SPAN>
JONES</SPAN>	3.24</SPAN>	1</SPAN>	FALSE</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=2></COLGROUP>

 

[delos001]

Sorry. I didn't realize you were sorting by customer too. There are a couple ways to pull out top 10. Unfortunately, I don't know how to do it with only using one column.

The easiest way is to use a PIVOT table which will allow you to automatically get the top 10.

The other way is to use two colums:

NAME	FCAST	SUM OF 10	RANK	TOP 10
SMITH	6.48	1	1	TRUE
SMITH	6	1	2	TRUE

<colgroup><col span="2" style="text-align: center;"><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>
</tbody>

In the Rank column: =IF(A2=A1,D1+IF(C2>0,1,0),IF(C2>0,1,0))
In the TOP 10 column: =IF(D2<=10,"TRUE","FALSE")

If you don't want two columns, you could forgo the TRUE FALSE indicators and go with conditional formatting to highlight top 10 and then you would be able to filter by color (instead of filtering by TRUE FALSE).

Someone with some advanced array knowledge might be able to help you do exactly what you want.

Thanks,

Jason

 

[adam087]

+1 for PivotTable...

/AJ

 

[NeonRedSharpie]

=IF(SUMIFS($C$2:C2,$A$2:A2,$A2,$C$2:C2,">"&0)<=10,"TRUE","FALSE")

Is this what you're looking for?

 

[tvman5683]

Thanks Jason. I'll look into your suggestions.
Red, your formula does what my exisiting one does. It also stops if the sum exceeds 10 on the last pick.
I'm looking to somehow move beyond that line and continue summing until I hit 10 for each customer in the list.
Like below it stops at 4. Sum would be 11. I need to skip the 4 and pick up the next lines that make 10. In this case it 3 more qty 1s

CUSTOMER</SPAN>	FCAST</SPAN>	QTY</SPAN>	TOP 10</SPAN>
SMITH</SPAN>	7.56</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	5.4</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	4.08</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	3.84</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	2.76</SPAN>	1</SPAN>	TRUE</SPAN>
SMITH</SPAN>	2.28</SPAN>	2</SPAN>	TRUE</SPAN>
SMITH</SPAN>	1.32</SPAN>	4</SPAN>	FALSE</SPAN>
SMITH</SPAN>	1.2</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	1.2</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.96</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.84</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.72</SPAN>	2</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.72</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.6</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.6</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.6</SPAN>	2</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.6</SPAN>	3</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.6</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.6</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.6</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.6</SPAN>	1</SPAN>	FALSE</SPAN>
SMITH</SPAN>	0.59</SPAN>	4</SPAN>	FALSE</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=3></COLGROUP>

JB

 

[NeonRedSharpie]

Thanks Jason. I'll look into your suggestions.
Red, your formula does what my exisiting one does. It also stops if the sum exceeds 10 on the last pick.
I'm looking to somehow move beyond that line and continue summing until I hit 10 for each customer in the list.
Like below it stops at 4. Sum would be 11. I need to skip the 4 and pick up the next lines that make 10. In this case it 3 more qty 1s


JB

Then you're probably not going to be able to do that with a formula. The logic required is going to be in UDF or macro/vba territory.

 

[tvman5683]

Thanks All