+ Reply to Thread
Results 1 to 3 of 3

Need Help on a data analysis problem

  1. #1
    Registered User
    Join Date
    07-02-2022
    Location
    Athens
    MS-Off Ver
    ms office 2021
    Posts
    2

    Need Help on a data analysis problem

    I would like to preface this by saying that I am new to Excel and that I have tried looking up a solution to my problem online. Whilst I will continue trying to find a solution on my own I figured there is no harm posting here.

    I will first describe the problem. My data consists of 20 columns. On the first row we have names, on the next thousand rows we have numbers. Each one of these thousands of rows represents an event, wherein the name with the highest number gets first place, the name with the second highest number gets second place and so on. My goal is to find how many times did name 1 get first place, repeat for name 2 and so on and then repeat that for every other place.

    I thought if I could analyze one row to give me first the name with the highest number on that row and then the name with the second highest number and so on, I would have 20 columns wherein the first column would have the name with the highest number, the second column would have the name with the second highest number and so on. I would then expand that analysis down 999 rows and then I would have in that first column the names of all the first place finishers. I could then read that column and get the results, say name 1 had x number of first places and so on, then repeat for every other column. Unfortunately my limited Excel knowledge didn't allow me to do that.

    I would appreciate any help either with making my solution work or with coming up with a better way to solve this. One less pressing issue is to find a way of dealing with ties but I have some ideas on that.
    Last edited by mckonto; 07-02-2022 at 04:45 PM. Reason: solved

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,819

    Re: Need Help on a data analysis problem

    So, basically, you want to sort each row from large to small, is what I am gathering from your description. Here's how I would expect to proceed (I hope you don't mind if I use column numbers rather than letters, since I don't want to try to convert 20+ columns to letters).

    1) The LARGE() function can easily be used to get the numbers in order. I would enter 1, 2, 3,...,20 in columns 21 to 40 of row 1. Then enter =LARGE($A2:$T2,U$1) in U2, note the mix of relative and absolute references, and copy down and across as far as needed. The largest number in each row is now in column 21 (U), the second largest in column 22 (V), and so on.
    2) A MATCH() function (exact match option) will readily determine which column contained each number. =MATCH(U2,$A2:$T2,0) (I think this would be in column AO -- column 41). Again, note the mix of relative and absolute references, and copy/paste/fill down and across as far as needed.

    2a) At this point, you can analyze the column numbers. If you decide you must analyze on name, then you can nest the MATCH() function in 2 inside of an INDEX() function to get names. =INDEX($A$1:$T$1,MATCH(...)). I'm inclined to think that the numeric analysis might be more efficient, then extract names with the INDEX() function at the end of the data analysis, but it would really be up to you.

    That should be a good start, any questions or difficulty with implementing those formulas?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    07-02-2022
    Location
    Athens
    MS-Off Ver
    ms office 2021
    Posts
    2

    Re: Need Help on a data analysis problem

    I managed to make them work, thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Problem with data table from what if analysis?
    By concepto44 in forum Excel General
    Replies: 1
    Last Post: 09-18-2018, 03:17 PM
  2. [SOLVED] Data Analysis Problem
    By GapWolf in forum Excel General
    Replies: 3
    Last Post: 05-25-2012, 06:40 AM
  3. Excel 2007 : Problem in Data Analysis and Ranking
    By imran9171 in forum Excel General
    Replies: 0
    Last Post: 11-13-2011, 03:34 PM
  4. VBA Data Analysis Loop Problem
    By darr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2011, 09:08 PM
  5. problem with data analysis
    By godel in forum Excel General
    Replies: 1
    Last Post: 08-19-2009, 12:00 PM
  6. data analysis problem
    By smalllittle in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-20-2009, 09:33 PM
  7. Excel 2007 : problem w/ data analysis add-in
    By adamseanor in forum Excel General
    Replies: 1
    Last Post: 11-21-2008, 12:59 PM

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