+ Reply to Thread
Results 1 to 7 of 7

Mode.Mult formula question

  1. 01-21-2017, 11:44 PM #1
    lucas257
    lucas257 is offline
    Registered User
    Join Date
    01-21-2017
    Location
    East Tawas, Michigan
    MS-Off Ver
    Office 2013
    Posts
    25

    Mode.Mult formula question

    I'm attempting to create a formula that reads a range of cells using MODE.MULT and an array to determine the top 5 most occurring numbers in order from greatest to least and have it continually update as data is entered. Here is the formula i am using now (which is not working)

    =MODE(IF(ISNA(MATCH($E$4:$E$499,$E$501:$E501,0)),$E$4:$E$499))

    This was giving me a circulation error for a reference in the formula so i went in and enabled iterations under formulas...still no success. Is all i am looking for is to get the top 5 most occurring numbers in order from greatest to least from E4:E499 and get the answers to read out starting in E501:e506...i'm no excel genius by any means so...at this point i'm just looking for an answer...i'm quite frustrated with this one. my file is attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-22-2017, 12:23 AM #2
    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.Mult formula question

    You are getting a circular reference error because your formula is in the same range (column E) as the range references.

    I'm looking at the rest.
    Dave
    Reply With QuoteRegister To Reply
  3. 01-22-2017, 03:02 AM #3
    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.Mult formula question

    Hi lucas257 and welcome to the forum.

    This proposed solution requires a helper column. Find it in P439:P498. The formula there is
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    It returns the counts of each number at its first occurrence.

    Then find this formula in E500:E504 where you had attempted the MODE formula.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-22-2017, 03:43 AM #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.Mult formula question

    Here's another.

    With helper range in F500:F504 and this formula.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Enter this array formula in E500 and fill down. 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.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-22-2017, 05:02 AM #5
    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.Mult formula question

    Another way with MODE function and without helpers. This is array entered in E500 and filled down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 01-22-2017, 12:25 PM #6
    lucas257
    lucas257 is offline
    Registered User
    Join Date
    01-21-2017
    Location
    East Tawas, Michigan
    MS-Off Ver
    Office 2013
    Posts
    25

    Re: Mode.Mult formula question

    THANK YOU!!! super helpful, got exactly what i needed! beyond helpful, all the ideas worked great; i did end up using the one without the helpers, but i experimented with all of them to understand them!

    Luke
    Last edited by lucas257; 01-22-2017 at 02:05 PM.
    Reply With QuoteRegister To Reply
  7. 01-22-2017, 09:43 PM #7
    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.Mult formula question

    You are welcome. Glad it works.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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. Trying to get the Mode, or Average if there's no MODE in a formula
    By jackmcguigan1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-18-2016, 05:00 PM
  2. Combine MODE.MULT with additional function?
    By Micobra in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-08-2015, 11:00 AM
  3. Mult. variables with known frequency. Formula?
    By sosborn2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2013, 03:57 PM
  4. [SOLVED] MODE.MULT - question
    By Saturn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-17-2012, 04:22 AM
  5. [SOLVED] Formula to Count based on Mult Criteria
    By stait in forum Excel General
    Replies: 3
    Last Post: 04-26-2012, 08:06 AM
  6. question on user-defined formula calculation mode: can it be instantaneous?
    By jerjer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2006, 05:39 AM
  7. I want to trace the precedents in a formula when they are on mult.
    By Paul in forum Excel General
    Replies: 3
    Last Post: 01-29-2005, 06:07 AM

Tags for this Thread

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