+ Reply to Thread
Results 1 to 9 of 9

Ranking question

Hybrid View

  1. #1
    Registered User
    Join Date
    10-11-2006
    Posts
    7

    Ranking question

    I'm making a spreadsheet to record the results of a horse sale, and I'm trying to make a summary sheet to list various statistics such as the 10 top sellers, 10 worst sellers, etc.

    Now, I know I can get the 10 top prices easily by using Large(<Range>, 1...10) in my cells but I can't figure out a way to properly determine the hip # and name. Everything works fine when there's only 1 entry at price n, but when there are multiple I run into problems.

    EX:

    Horse 1 sold for $5500
    Horse 2 sold for $8500
    Horse 3 sold for $5500
    Horse 4 sold for $6000

    So, my summary should show
    Horse 2 $8500
    Horse 4 $6000
    Horse 1 $5500
    Horse 3 $5500


    Is there any way to do this?

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    See links.

    Post back if you still have a problem

    http://www.cpearson.com/excel/Rank.aspx

    http://www.techonthenet.com/excel/formulas/large.php

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    10-11-2006
    Posts
    7
    I see how it works, but I'm not sure it's going to help me the way I want. The way I was doing it before was this

    [SALE RESULTS]
    A2:A26 -- Hip #
    B2:B26 -- Name
    H2:H26 -- Selling Price

    [SUMMARY]
    A1(Hip #) --> =SUMPRODUCT(('Sheet1'!$H$2:$H$26 = C1)*('Sheet1'!$A$2:$A$26))
    B1(Name) --> = INDIRECT("'Sheet1'!B" & A1 + 1)
    C1(Price) --> =LARGE('Sheet1'!$H$2:$H$26, ROW())


    What I get returned right now is this:
    10	Stormy Song		$8,500
    11	Rustico Joey		$6,200
    16	Up With The Birds	$5,500
    16	Up With The Birds	$5,500
    14	Dunmore Bling		$5,000
    36	0			$2,700
    36	0			$2,700
    19	Nabbed			$2,600
    21	Dusty Lane Ashlyn	$2,500
    17	Accolade Seelster	$2,100
    The problem with this is:

    There were 2 horses that sold for $5500... Hip #s 4 and 12 (4 + 12 = 16)... there were also 2 that sold for $2700... Hip #s 16 and 20 (16 + 20 = 36)

    The output I would like it this
    10	Stormy Song		$8,500
    11	Rustico Joey		$6,200
    4	Meadowbank		$5,500
    12	Dunmore Chip		$5,500
    14	Dunmore Bling		$5,000
    16	Up With The Birds	$2,700
    20	Debs Nabby		$2,700
    19	Nabbed			$2,600
    21	Dusty Lane Ashlyn	$2,500
    17	Accolade Seelster	$2,100

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    A sample spreadsheet might be easier to see what the issue is

    VBA Noob

  5. #5
    Registered User
    Join Date
    10-11-2006
    Posts
    7
    The spreadsheet is at http://www.harnesspages.com/files/sale.xls

    The 2nd sheet, 2007 PEI Trotters Summ, is the one I'm trying to produce this summary on.

    Also, is there any way to open OpenDocument files in Office 2007?

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    See if this helps

    VBA Noob
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1