Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: =mode(if(and help

  1. #1
    New Member
    Join Date
    Jun 2014
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default =mode(if(and help

    I am trying to calculate the mode of our turn-around times for cases we handle as follows:

    In column N, which contains whole numbers, which I want to obtain the mode values of, if:

    • Column A equals the name of a person in my team reference by cell J2
    • if the date in column I was greater than 31/12/12 (referenced J1) and less than 01/01/2013 (referenced by K1)

    So far I can get the below to work based only on using greater than 31/12/12 and not with the less than option as follows:

    {=MODE(IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$A$2:$A$56366=J2,IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$I$2:$I$56366
    I have tried adding in a AND statement but cannot seem to get this to work, any ideas?

  2. #2
    Board Regular Dreadknight Nasus's Avatar
    Join Date
    Jun 2013
    Location
    Top Lane
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =mode(if(and help

    I don't believe you pasted the whole formula?
    "Such is the human race, often it seems a pity that Noah... didn't miss the boat."
    Mark Twain

  3. #3
    New Member
    Join Date
    Jun 2014
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =mode(if(and help

    Quote Originally Posted by Dreadknight Nasus View Post
    I don't believe you pasted the whole formula?
    For some reason it won't display it in full and I don't seem to be able to attach an example file, will try again shortly....
    Last edited by chrischilton01; Jun 9th, 2014 at 01:35 PM.

  4. #4
    Board Regular Dreadknight Nasus's Avatar
    Join Date
    Jun 2013
    Location
    Top Lane
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =mode(if(and help

    Wrap it in code tags. use "[ code ]" and "[ /code]" without the spaces
    "Such is the human race, often it seems a pity that Noah... didn't miss the boat."
    Mark Twain

  5. #5
    New Member
    Join Date
    Jun 2014
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =mode(if(and help

    Quote Originally Posted by Dreadknight Nasus View Post
    Wrap it in code tags. use "[ code ]" and "[ /code]" without the spaces
    Ok mmm still struggling this, can you show me an actual example of how you wrap it in code tags please?
    Last edited by chrischilton01; Jun 10th, 2014 at 04:59 AM.

  6. #6
    Board Regular Dreadknight Nasus's Avatar
    Join Date
    Jun 2013
    Location
    Top Lane
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =mode(if(and help

    *[code*]{=MODE(IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$A$2:$A$56366=J2,IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$I$2:$I$56366...[*/code]*


    Get rid of the red asterisks and finish the line of code
    "Such is the human race, often it seems a pity that Noah... didn't miss the boat."
    Mark Twain

  7. #7
    New Member
    Join Date
    Jun 2014
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =mode(if(and help

    Quote Originally Posted by Dreadknight Nasus View Post
    *[code*]{=MODE(IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$A$2:$A$56366=J2,IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$I$2:$I$56366...[*/code]*


    Get rid of the red asterisks and finish the line of code
    Hope this works, thanks for your help.

    *[{=MODE(IF('N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$A$2:$A$56366=J2,IF('N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$I$2:$I$56366

  8. #8
    New Member
    Join Date
    Jun 2014
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =mode(if(and help

    Dear me, will try share via dropbox instead, what a ball ache trying to paste in to here is...
    Last edited by chrischilton01; Jun 10th, 2014 at 11:05 AM.

  9. #9
    Board Regular Dreadknight Nasus's Avatar
    Join Date
    Jun 2013
    Location
    Top Lane
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =mode(if(and help

    Nope, it should look like this
    Code:
    {=MODE(IF('N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$A$2:$A$56366=J2,IF('N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$I$2:$I$56366
    "Such is the human race, often it seems a pity that Noah... didn't miss the boat."
    Mark Twain

  10. #10
    New Member
    Join Date
    Jun 2014
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =mode(if(and help

    [code]{=MODE(IF('N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$A$2:$A$56366=J2,IF('N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$I$2:$I$56366}code]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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