Menu

rank

Search this Thread

Prev

Previous Post

Next Post

Next

#3

Report Post

Posted to microsoft.public.excel.misc

Leo Heuser Leo Heuser is offline

Leo Heuser is offline

external usenet poster

Posts: 266

Default

rank

"Lori Maurer" <Lori skrev i en meddelelse
...
I have a list of data that I need to rank from 1-?? based on two sets of
criteria. here is an example of what I need.

Customer Name Product Sales Rank

Customer A Product 1 100 1
Customer A Product 1 50 3
Customer A Product 1 75 2
Customer A Product 2 540 2
Customer A Product 2 390 3
Customer A Product 2 600 1

So my question is, when Product changes, I want the ranking to start over
again. Is there a formula I can use to do this? Can I use an if
statement
combined with a rank? (The product is not a number, it's text)

Thank you!!!!!

Hi Lori

One way, assuming your data in A2:C100 and assuming all elements
of the same product are grouped as shown:

In D2 enter this array formula as one line:

=MATCH(C2,LARGE(OFFSET($C$2,MATCH(B2,$B$2:$B$100,0 )-1,0,
MATCH(B2,$B$2:$B$100,0)+COUNTIF($B$2:$B$100,B2)-1),
ROW(INDIRECT("1:"&COUNTIF($B$2:$B$100,B2)))),0)

The formula must be finished with <Shift<Ctrl<Enter instead of just
<Enter, also if edited later. If done correctly, Excel will display the
formula in the formula bar enclosed in braces { }. Don't enter these
braces yourself, they're Excel's way of showing, that the formula is an
array formula.

Copy D2 down with the fill handle (the little square in the lower corner of
the cell).

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Rank & Group Tournament	chad	Excel Discussion (Misc queries)	0	September 20th 06 05:35 PM
RANK Function - Zero Value Ranked as 1 - Should be 10	Sandi	Excel Worksheet Functions	8	August 4th 06 03:05 AM
Rank where lowest value is highest rank	mile3024	Excel Worksheet Functions	2	December 9th 05 10:57 PM
RANK formula	KG	Excel Discussion (Misc queries)	3	September 23rd 05 03:27 PM
Rank fx - Fill Down?	Steve B	Excel Worksheet Functions	1	February 23rd 05 08:28 PM

All times are GMT +1. The time now is 07:49 AM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement