+ Reply to Thread
Results 1 to 13 of 13

creating a formula that will calculate the mode of a value in a table.

  1. 09-11-2015, 02:36 PM #1
    zachary.stucki
    zachary.stucki is offline
    Registered User
    Join Date
    09-11-2015
    Location
    phoenix, arizona, USA
    MS-Off Ver
    2016
    Posts
    15

    Angry creating a formula that will calculate the mode of a value in a table.

    So:
    I have a big table (approximately 53,000 rows of data and over 75 columns).
    I want to calculate the mode for a specific subset of the data so I was using a vlookup to isolate the subset of data, then I was using the mode to calculate the mode.

    Here is the formula I have been trying to use - =VLOOKUP(A2,Table3,MODE(IF(Sheet1!$BK:$BK=Sheet1!$BX:$BX,VLOOKUP(A2,Table2,26,FALSE),Sheet1!BK14),),FALSE)

    If you break it down this part of the formula works: IF(Sheet1!$BK:$BK=Sheet1!$BX:$BX,VLOOKUP(A2,Table2,26,FALSE),Sheet1!BK14)
    Once I add the mode it stops working.

    I want to get this to work because it could be a big monetary boost for our company.
    I am no VBA expert, but I do know a little about it so I am willing to look into using VBA as a solution.
    Reply With QuoteRegister To Reply
  2. 09-11-2015, 04:05 PM #2
    Craig K.
    Craig K. is offline
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: creating a formula that will calculate the mode of a value in a table.

    =MODE(IF(A2:A100=A2,B2:B100))

    Confirm using Ctrl+Shift+Enter, not just Enter! Adjust formula to your ranges and specs.

    This formula will find the mode of values in column B where column A is equal to the value in cell A2.
    Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.

    Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
    If you're happy with an answer given, please click the * under the person's name to boost their reputation.
    Reply With QuoteRegister To Reply
  3. 09-11-2015, 05:12 PM #3
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    Win10/MSO2016
    Posts
    12,966

    Re: creating a formula that will calculate the mode of a value in a table.

    I don't understand your formula. You said you want to calculate the MODE of a subset of a table; I take subset to mean a block of cells within table3. However, you are using an outer VLOOKUP which will return a single value from a column in table3 specified by the MODE function. Since you said the table only has about 75 columns, the most frequently found number would have to be less than or equal to the number of columns in the table.

    So... How are you determining the top left cell, the height and width of the subset?

    Seems to me that you would want:
    MODE(OFFSET(Table3,some row, some column, height, width)) or
    MODE(INDEX(...

    You didn't post a wk so I can't experiment...
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  4. 09-11-2015, 07:04 PM #4
    zachary.stucki
    zachary.stucki is offline
    Registered User
    Join Date
    09-11-2015
    Location
    phoenix, arizona, USA
    MS-Off Ver
    2016
    Posts
    15

    Re: creating a formula that will calculate the mode of a value in a table.

    tried it. No luck.
    Reply With QuoteRegister To Reply
  5. 09-11-2015, 07:05 PM #5
    zachary.stucki
    zachary.stucki is offline
    Registered User
    Join Date
    09-11-2015
    Location
    phoenix, arizona, USA
    MS-Off Ver
    2016
    Posts
    15

    Re: creating a formula that will calculate the mode of a value in a table.

    is there any way that I can post an example wk so you can experiment, or do I need to start a new thread or send you a private email?
    Reply With QuoteRegister To Reply
  6. 09-11-2015, 07:31 PM #6
    zachary.stucki
    zachary.stucki is offline
    Registered User
    Join Date
    09-11-2015
    Location
    phoenix, arizona, USA
    MS-Off Ver
    2016
    Posts
    15

    Re: creating a formula that will calculate the mode of a value in a table. 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 09-11-2015, 07:33 PM #7
    zachary.stucki
    zachary.stucki is offline
    Registered User
    Join Date
    09-11-2015
    Location
    phoenix, arizona, USA
    MS-Off Ver
    2016
    Posts
    15

    Re: creating a formula that will calculate the mode of a value in a table. 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 09-11-2015, 09:10 PM #8
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    Win10/MSO2016
    Posts
    12,966

    Re: creating a formula that will calculate the mode of a value in a table.

    (How to) Attach a Workbook
    • Click Go Advanced, next to "Post Quick Reply" (lower right of edit box),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Reply With QuoteRegister To Reply
  9. 09-14-2015, 11:58 AM #9
    zachary.stucki
    zachary.stucki is offline
    Registered User
    Join Date
    09-11-2015
    Location
    phoenix, arizona, USA
    MS-Off Ver
    2016
    Posts
    15

    Re: creating a formula that will calculate the mode of a value in a table.

    excel example.xlsx

    Here is the example file.
    Reply With QuoteRegister To Reply
  10. 09-14-2015, 03:09 PM #10
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    Win10/MSO2016
    Posts
    12,966

    Re: creating a formula that will calculate the mode of a value in a table.

    post deleted
    Last edited by protonLeah; 09-15-2015 at 01:45 PM.
    Reply With QuoteRegister To Reply
  11. 09-15-2015, 10:25 AM #11
    Craig K.
    Craig K. is offline
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: creating a formula that will calculate the mode of a value in a table.

    Not to knock the formula proton gave, but the one I gave you is more compact and does work in your sample workbook provided you reformat the numbers in the Mode Data tab like proton pointed out.

    The formula, specifically for your workbook, to put into cell C2 is =MODE(IF(DATA!$C$112:$C$6268=A2,DATA!$D$112:$D$6268)) entered with Ctrl+Shift+Enter
    Reply With QuoteRegister To Reply
  12. 09-15-2015, 02:14 PM #12
    zachary.stucki
    zachary.stucki is offline
    Registered User
    Join Date
    09-11-2015
    Location
    phoenix, arizona, USA
    MS-Off Ver
    2016
    Posts
    15

    Re: creating a formula that will calculate the mode of a value in a table.

    does this formula remove 0 as a valid return entry?
    Reply With QuoteRegister To Reply
  13. 09-15-2015, 03:22 PM #13
    Craig K.
    Craig K. is offline
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: creating a formula that will calculate the mode of a value in a table.

    Use this to avoid 0 values and leave a blank when there is no match
    =IFERROR(MODE(IF(DATA!$C$112:$C$6306=A2,IF(DATA!$D$112:$D$6306<>0,DATA!$D$112:$D$6306))),"")
    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. Need to calculate median and mode similarly to how it could be done in a pivot table
    By cheddarcheese in forum Excel General
    Replies: 4
    Last Post: 12-04-2014, 05:55 PM
  2. how to calculate the mode
    By jakeembx in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-09-2013, 05:57 PM
  3. how to calculate the mode
    By jakeembx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-09-2013, 08:07 AM
  4. Need help creating formula to calculate discounts
    By Kwirky in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-29-2010, 11:43 PM
  5. When in TABLE mode, how to auto-copy formula when creating new entry ?
    By brazool in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-21-2008, 09:06 AM
  6. Creating a formula to calculate age...
    By homeuser in forum Excel General
    Replies: 3
    Last Post: 09-25-2006, 02:19 PM
  7. Using a MODE formula in a Pivot Table
    By GoDario27 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-19-2006, 01:46 PM

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