+ Reply to Thread
Results 1 to 5 of 5

Mode keeps returning #n/a

  1. #1
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    Mode keeps returning #n/a

    The arguments are a column of division formulas, like c4/d4 all equaling around 100-120 with plenty of duplicates(I read are required to avoid #n/a). Nothing fancy or fishy about it. What's the problem now excel?? Thanks

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Mode keeps returning #n/a

    Hi,

    Almost impossible to say without seeing the workbook. Any chance you could upload it or at least the relevant bit?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468
    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Almost impossible to say without seeing the workbook. Any chance you could upload it or at least the relevant bit?
    Hopefully this works...
    Attached Files Attached Files
    Excel 1.0.1 (16.0.14326.20140) (Android)
    Excel 2010 14.04.4760.1000

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Mode keeps returning #n/a

    If you display some more decimals, you'll see that there are no repeats.

    You could change in formula in D4 and down to

    =ROUND(A4/B4, 0)

    .. then you'll see the mode (107).

    I expect, though, that you may want the median, not the mode. For that, leave the formulas as is, and change MODE to MEDIAN (108.26)
    Last edited by shg; 04-10-2016 at 04:01 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Mode keeps returning #n/a

    Hi,

    Mode always returns #N/A if there are no duplicates. Your column D values are all unique hence you get #N/A

    If you only want to consider the Integer values then use =MODE(INT(D4:D38))

+ 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. [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. VBA AutoFilter does not Work in run mode, only in debug mode
    By aganove in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 11-19-2015, 04:32 PM
  3. VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode
    By valerie.k.chiang in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2014, 03:48 PM
  4. [SOLVED] Code skips on Run Mode but runs fine on F8 (Debug Mode)
    By RaquelAR in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-25-2013, 01:05 PM
  5. Excel 2010: Mode.Sngl not returning lowest of multiple modes
    By yukontornado in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-17-2013, 04:26 PM
  6. Can I use VBA to check if the sheet(xxx) is in Page-Break mode or Normal mode ?
    By BaLLZaCH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2008, 09:03 AM
  7. Replies: 0
    Last Post: 01-07-2005, 04:06 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