+ Reply to Thread
Results 1 to 4 of 4

MODE vs INDEX

  1. 11-25-2016, 09:59 PM #1
    slikfuk
    slikfuk is offline
    Registered User
    Join Date
    11-25-2016
    Location
    Newark, NJ
    MS-Off Ver
    2016
    Posts
    1

    Exclamation MODE vs INDEX

    I'm trying to calculate the frequency of recurring numbers from multiple columns. I used MODE (=MODE($B$4:$B$108))) in the first row for the most frequent number, and for the 2nd most frequent I used

    =INDEX(ROW(INDIRECT("B"&MIN($B$4:$B$108)&":B"&MAX($B$4:$B$108))),MATCH(LARGE(FREQUENCY($B$4:$B$108,ROW(INDIRECT("B"&MIN($B$4:$B$108)&":B"&MAX($B$4:$B$108)))),2),FREQUENCY($B$4:$B$108,ROW(INDIRECT("B"&MIN($B$4:$B$108)&":B"&MAX($B$4:$B$108)))),FALSE))

    However, when I change highlighted number to '3' for the third most frequent (and so on for 4th and 5th), I end up with a repeated number. The second (and subsequent) formula was entered as an array, but after three days, I've given up trying to figure it out. I really do need help understanding what I am doing incorrectly.

    Thanks in advance for any assistance you can provide.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-26-2016, 01:19 AM #2
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: MODE vs INDEX

    Hello,

    Try this Array Formula in J6, then copy across & down.

    =MODE(IF(ISNA(MATCH(B$4:B$108,J$5:J5,0)),IF(B$4:B$108<>0,B$4:B$108)))
    Regards,
    Haseeb Avarakkan
    __________________________________
    "Feedback is the breakfast of champions"
    Reply With QuoteRegister To Reply
  3. 11-26-2016, 02:13 AM #3
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,451

    Re: MODE vs INDEX

    Welcome to the forum! Unfortunately your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 11-27-2016, 03:33 PM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: MODE vs INDEX

    Another way.

    Try array entering this in J5. Fill down and across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    It returns the expected values in the upload.
    Dave
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDEX(MATCH(MODE) Formula returning #N/A
    By shellataylor in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-19-2016, 08:40 PM
  2. [SOLVED] Index/mode formula
    By TanyaMatt in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-10-2015, 11:03 AM
  3. Match, Mode and Index... And Dropdowns?
    By Oilfield in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-12-2015, 07:11 PM
  4. [SOLVED] MODE and Index Match Formula
    By dash11 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-21-2015, 01:32 PM
  5. Index/Match & Mode?
    By WWSL14 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 04:04 PM
  6. Request help listing top occurring values in list -index/mode/match array
    By jerrygoldsmith in forum Excel General
    Replies: 14
    Last Post: 11-30-2012, 02:54 PM
  7. Index, Mode & Match Problem?
    By BodyHaven in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-10-2008, 06:53 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1