Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2013
    Posts
    17
    Thanks
    14
    Thanked 1 Time in 1 Post

    problem with MODE function

    FOR EXCEL 2010:
    I have 3 cells in a row where I am asking people to input numbers. Each cell represents a question number on a survey . For example, the prompt is "Enter the highest scored item numbers (up to three)" and respondents enter 5, 18, and 20 in cells b7, c7, and d7.

    I have created a formula in another cell where this question is coded to display the survey dimension in which each of these qusetions falls. that formula is: =IF(OR(B7={4,10,11,12,13,15,16,17,18,19,20,21}),"p hysical dimension",IF(OR(B7={1,2,3,5,6,7,8,9,14}),"cogniti ve dimension")).

    I am TRYING to get a MODE for this data to know which dimension is reported most frequently of the 3. In other words, do their 3 highest scores fall most often in cognitive or physical dimension.MODE didnt work but I thought it was because it was an alphabetic value in the cells so I tried coverting that cell to a number with the formula =IF(OR(B7={4,10,11,12,13,15,16,17,18,19,20,21}),"1 ",IF(OR(B7={1,2,3,5,6,7,8,9,14}),"2")) and it still won't let me run a mode formula on the 3 cells!


    Ultimately, what I am trying to do is get this cell to generate so I can automatically plug it into a sentence from another cell. To continue with the example, if the highest three questions are 5, 18, and 20. There would be 2 physical and 1 cognitive dimension. I am trying to get a cell that will SAY "physical" after figuring this out rather than me having to look at the 3 cells adn seeing 2 v. 1. This "physical" value will then plug in automatically into a sentence (housed in another cell) that says "Their highest score(s) were on measures of ______." using the formula =(CELL WITH SENTENCE&" "&CELL WITH PHYSICAL AS THE MODE&".") I'm also gonna use that info to generate the next sentence that explains the cell. My plan is to then use an IF/OR function depending on whether the mode is physical or cognitive. So essentially, its the same cell dictating multiple points of data and I can't figure it out!

    I hope this is clear enough...any thoughts??? I've googled my brains out to no avail!

    p.s. excel and forum noobie so speak slowly lol

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    MODE should work on the cells if the values are numbers not text:
    =IF(OR(B7={4,10,11,12,13,15,16,17,18,19,20,21}),1, IF(OR(B7={1,2,3,5,6,7,8,9,14}),2))

    You could also use a countif to count the number of "Physical dimensions" (or "cognitive dimension") if you want the formulas like:
    =IF(OR(B7={4,10,11,12,13,15,16,17,18,19,20,21}),"p hysical dimension",IF(OR(B7={1,2,3,5,6,7,8,9,14}),"cogniti ve dimension"))

    I would put a formula, but do not know what cells the IF(or... formulas are in...
    [perhaps you could upload an example file...]

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    kissingfrogs2003 (2013-06-05)

  4. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    MODE operates on numbers vs text, as you discovered. In the latter formula, remove the quotes from around the 1 and the 2 so that you actually put in numbers rather than alphanumeric values. Then, mode should work.

    I put your formula in A1, B1, and C1 and then in another cell used: =IF(1=MODE(A1:C1),"Cognitive","Physical")

    Is this what you're trying to get?

    Kevin

  5. The Following User Says Thank You to kweaver For This Useful Post:

    kissingfrogs2003 (2013-06-05)

  6. #4
    New Lounger
    Join Date
    Jun 2013
    Posts
    17
    Thanks
    14
    Thanked 1 Time in 1 Post
    OMG YES that worked great- thanks to both of you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •