+ Reply to Thread
Results 1 to 99 of 99

Mode-1

  1. #1
    Registered User
    Join Date
    07-26-2005
    Posts
    3

    Mode-1

    I need to figure something out on my sheet. The “mode” function tells you witch value repeats the most. I want to see if there is another value that repeats just as much. Also, I want to see what value repeats the second most.

    Ex:
    4
    6
    4
    7
    5
    6
    4
    5
    5

    If I use the function: =mode(list). It will return, “4”. By using =countif((list),mode(list)) I see that that repeated 3 times.

    I want a way to see that “5” also repeated 3 times and then that 6 is repeated 2 times.

  2. #2
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  3. #3
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  4. #4
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  5. #5
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  6. #6

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  7. #7
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  8. #8
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  9. #9
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  10. #10

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  11. #11
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  12. #12
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  13. #13
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  14. #14
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  15. #15

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  16. #16
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  17. #17
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  18. #18
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  19. #19
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  20. #20
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  21. #21
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  22. #22
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  23. #23
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  24. #24
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  25. #25

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  26. #26
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  27. #27

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  28. #28
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  29. #29
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  30. #30
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  31. #31
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  32. #32
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  33. #33
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  34. #34
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  35. #35
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  36. #36

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  37. #37
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  38. #38
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  39. #39

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  40. #40
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  41. #41
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  42. #42
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  43. #43
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  44. #44
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  45. #45
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  46. #46
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  47. #47
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  48. #48

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  49. #49
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  50. #50
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  51. #51

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  52. #52
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  53. #53
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  54. #54
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  55. #55
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  56. #56

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  57. #57
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  58. #58
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  59. #59
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  60. #60
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  61. #61
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  62. #62

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  63. #63
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  64. #64
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  65. #65
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  66. #66
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  67. #67
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  68. #68
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  69. #69
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  70. #70
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  71. #71
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  72. #72

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  73. #73
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  74. #74
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  75. #75
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  76. #76

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  77. #77
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  78. #78
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  79. #79
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  80. #80

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  81. #81
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  82. #82
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  83. #83
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  84. #84

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  85. #85
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  86. #86
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  87. #87
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  88. #88

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  89. #89
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  90. #90

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  91. #91
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  92. #92
    Harlan Grove
    Guest

    Re: Mode-1

    Aladin Akyurek wrote...
    ....
    >--
    >[1] The SumProduct function should implicitly coerce the truth values to
    >their Excel numeric equivalents.
    >[2] The lookup functions should have an optional argument for the return
    >value, defaulting to #N/A in its absence.


    Interesting signature, but the first would require SUMPRODUCT to work
    differently than other functions with respect to boolean TRUE. If you
    want that functionality, you could always switch to Gnumeric.

    As for the second, it'd be nice if there were an error trapping
    function or even better a general trapping function: a function that
    would return the value of its 1st argument if it satisfied its 2nd
    COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    argument otherwise, with the default value for the 3rd argument being
    VBA Empty, so "" in text contexts or 0 in numeric contexts.

    Still, I'd prefer eliminating the artificial 7 nested function call
    limit and allow different files with the same base filename to be open
    at the same time, no matter how much recoding would be necessary.


  93. #93
    Max
    Guest

    Re: Mode-1

    One way to play with ..

    Assuming the sample data is in A1:A9

    Put in:

    B1:
    =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())

    C1:
    =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
    :A1)),B:B,0)))

    D1:
    =COUNTIF(A:A,C1)

    E1:
    =IF(D1=0,"",D1-ROW()/10^10)

    F1:
    =IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C:C,MATCH(LARGE($E:$E,ROWS(
    $A$1:A1)),$E:$E,0)))

    Copy F1 across to G1

    Select B1:G1, fill down to G9

    Cols F & G will return the desired results:

    4 3
    5 3
    6 2
    7 1

    where:

    col F = unique numbers within source range A1:A9
    col G = occurence count of the unique numbers

    The listing in col F will be sorted in descending order by the counts in col
    G. Ties, if any, will be listed in the same relative order that the unique
    numbers appear in the source range.

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:

    http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  94. #94
    Mayank Prakash Gupta
    Guest

    Re: Mode-1

    try using pivot tables...it will show the count of all the numbers


    "DArinello" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I need to figure something out on my sheet. The "mode" function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, "4". By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that "5" also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >
    > --
    > DArinello
    > ------------------------------------------------------------------------
    > DArinello's Profile:
    > http://www.excelforum.com/member.php...o&userid=25589
    > View this thread: http://www.excelforum.com/showthread...hreadid=390089
    >




  95. #95

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  96. #96
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


  97. #97
    Aladin Akyurek
    Guest

    Re: Mode-1

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=159292

    DArinello wrote:
    > I need to figure something out on my sheet. The “mode” function tells
    > you witch value repeats the most. I want to see if there is another
    > value that repeats just as much. Also, I want to see what value
    > repeats the second most.
    >
    > Ex:
    > 4
    > 6
    > 4
    > 7
    > 5
    > 6
    > 4
    > 5
    > 5
    >
    > If I use the function: =mode(list). It will return, “4”. By using
    > =countif((list),mode(list)) I see that that repeated 3 times.
    >
    > I want a way to see that “5” also repeated 3 times and then that 6 is
    > repeated 2 times.
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  98. #98

    Re: Mode-1


    Harlan Grove wrote:
    > Aladin Akyurek wrote...
    > ...
    > >--
    > >[1] The SumProduct function should implicitly coerce the truth values to
    > >their Excel numeric equivalents.
    > >[2] The lookup functions should have an optional argument for the return
    > >value, defaulting to #N/A in its absence.

    >
    > Interesting signature, but the first would require SUMPRODUCT to work
    > differently than other functions with respect to boolean TRUE. If you
    > want that functionality, you could always switch to Gnumeric.


    The proposal is SumProduct for it would be less desirable to have:

    =SUM(1,2,TRUE)

    to yield 4. Or

    =MAX(0.2,0.4,TRUE)

    to yield 1.

    >
    > As for the second, it'd be nice if there were an error trapping
    > function or even better a general trapping function: a function that
    > would return the value of its 1st argument if it satisfied its 2nd
    > COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    > argument otherwise, with the default value for the 3rd argument being
    > VBA Empty, so "" in text contexts or 0 in numeric contexts.
    >


    [...]

    That would not pre-empt my proposal, would it?


  99. #99
    Harlan Grove
    Guest

    Re: Mode-1

    [email protected] wrote...
    >Harlan Grove wrote:

    ....
    >>As for the second, it'd be nice if there were an error trapping
    >>function or even better a general trapping function: a function that
    >>would return the value of its 1st argument if it satisfied its 2nd
    >>COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
    >>argument otherwise, with the default value for the 3rd argument being
    >>VBA Empty, so "" in text contexts or 0 in numeric contexts.

    >
    >That would not pre-empt my proposal, would it?


    I believe it would. It would also allow some chance of backward
    compatibility. If TRAP were added to Excel XVI, formulas like

    =TRAP(VLOOKUP(x,y,z,0),"<>#*","no "&x&" found")

    could be used in older versions if an add-in TRAP function were
    provided, but

    =VLOOKUP(x,y,z,0,"no "&x&" found")

    would cause problems in older versions.

    Now if Microsoft were to consider canibalizing their spreadsheet market
    share and sell a new & different spreadsheet, then there are LOTS of
    things to change. But I doubt that'd happen unless & until they lost
    serious market share, and FWIW OpenOffice Calc isn't good enough yet,
    and Gnumeric is too slow under Windows.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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