Top Ten Formula

tvman5683

Board Regular
Joined
Mar 23, 2009
Messages
90
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.

HTML:
=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>
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

delos001

New Member
Joined
Oct 7, 2014
Messages
25
Hey,

Try this in the TOP 10 column:

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

tvman5683

Board Regular
Joined
Mar 23, 2009
Messages
90
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

New Member
Joined
Oct 7, 2014
Messages
25
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:

NAMEFCASTSUM OF 10RANKTOP 10
SMITH6.4811TRUE
SMITH612TRUE

<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

Well-known Member
Joined
Jun 7, 2010
Messages
1,356

ADVERTISEMENT

+1 for PivotTable...

/AJ
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
=IF(SUMIFS($C$2:C2,$A$2:A2,$A2,$C$2:C2,">"&0)<=10,"TRUE","FALSE")

Is this what you're looking for?
 

tvman5683

Board Regular
Joined
Mar 23, 2009
Messages
90

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,491
Messages
5,523,255
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top