+ Reply to Thread
Results 1 to 12 of 12

How to use small function with nested formulas

  1. #1
    Registered User
    Join Date
    02-24-2019
    Location
    UK
    MS-Off Ver
    MS 365 PC & Mac (assume PC unless otherwise stated in post)
    Posts
    96

    How to use small function with nested formulas

    I am trying to use SMALL function to return 2nd largest value, but SMALL does not seem to like nested formulas ("There's a problem with this formula." message). Can anyone suggest how I can either adapt my formula or perhaps propose an alternative solution?

    I want to do it without the use of helper columns/cells.

    Essentially I want to return the count of the 2nd most frequent character (In some scenarios 2nd most frequent could be the same as MAX).

    Contents in cell A1 is AAABBBBCC

    Using below max formula I get the answer 4, as expected;

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But when I try and adapt to SMALL it does not work;

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I attach above in worksheet also. I am using Mac Excel incase that is relevant.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: How to use small function with nested formulas

    Is the version of Excel you are using up-to-date in your profile? I ask because if you have 365, things will be a lot easier.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    02-24-2019
    Location
    UK
    MS-Off Ver
    MS 365 PC & Mac (assume PC unless otherwise stated in post)
    Posts
    96

    Re: How to use small function with nested formulas

    It is accurate now, I just updated my profile to read 16.6 instead of 16.5. I do have a 365 subscription, how does that make it a lot easier?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: How to use small function with nested formulas

    OK - so in your profile you need to state that you have 365 for Mac (frankly the release number is by the by and of no real interest to us).

    365 has a lot of new functions that earlier versions don't have that may make solutions shorter and more efficient.

    That said, SMALL cannot be used with an array built in the same way that MAX, MIN, SUM, etc. can, so how about this?

    =MEDIAN(LEN(A2)-LEN(SUBSTITUTE(A2,"A","")),LEN(A2)-LEN(SUBSTITUTE(A2,"B","")),LEN(A2)-LEN(SUBSTITUTE(A2,"C","")))

    By the way, neither of the formulae in your opening post will return anything: A1 is not the cell they need to refer to.
    Last edited by AliGW; 04-24-2022 at 04:14 AM.

  5. #5
    Registered User
    Join Date
    02-24-2019
    Location
    UK
    MS-Off Ver
    MS 365 PC & Mac (assume PC unless otherwise stated in post)
    Posts
    96

    Re: How to use small function with nested formulas

    Ok, understood thanks.

    No unfortunately Median would not work for this, below I list expected scenarios and the 2nd value I would want to return;

    4,1,0 return 1
    3,1,1 return 1
    3,2,0 return 2
    2,2,1 return 2
    2,1,1,1 return 1
    1,1,1,1,1 return 1

    Formula in my original post was an example, where there will actually be 5 nested formula looking at A, B, C, D and E

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: How to use small function with nested formulas

    OK - so yor example should reflect the real data, please, otherwise solutions offered may not work.

    Please update your profile as requested whilst I have another think.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How to use small function with nested formulas

    Please try at B4

    =LET(z,A4,u,UNIQUE(MID(z,SEQUENCE(LEN(z)),1)),LARGE(LEN(z)-LEN(SUBSTITUTE(z,u,)),2))
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-24-2019
    Location
    UK
    MS-Off Ver
    MS 365 PC & Mac (assume PC unless otherwise stated in post)
    Posts
    96

    Re: How to use small function with nested formulas

    Bo_Ry, yes that seems to do the trick, thank you.

    Honestly speaking I don't understand it but I will go away and break it down for my understanding.

    Much appreciated!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: How to use small function with nested formulas

    If you want the second smallest and you have more than three letters, then you will need to change it to this:

    =LET(z,A2,u,UNIQUE(MID(z,SEQUENCE(LEN(z)),1)),SMALL(LEN(z)-LEN(SUBSTITUTE(z,u,)),2))

    If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  10. #10
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,277

    Re: How to use small function with nested formulas

    Power Query
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: How to use small function with nested formulas

    The PQ solution returns the minimum, not the second smallest.

  12. #12
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,277

    Re: How to use small function with nested formulas

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Czeslaw; 04-25-2022 at 01:23 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 03-02-2021, 10:50 PM
  2. Searching for values in a small list without using nested IFs
    By crstfrdrnt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2020, 10:56 PM
  3. Replies: 2
    Last Post: 01-05-2019, 03:33 AM
  4. #DIV/0! Error - Most of the formulas work, except for a small few?!
    By vkz7hxy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2016, 01:03 PM
  5. How Small Function works when small(array,1),small(array,2) are same ?
    By bkvenkat in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-02-2015, 02:00 AM
  6. [SOLVED] Help Requested with Nested Small Function Inside Index/Match Formula
    By trandle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2015, 10:07 AM
  7. [SOLVED] Iserror works on one column but not two in small nested formula
    By Philb1 in forum Excel General
    Replies: 8
    Last Post: 07-07-2012, 02:08 AM

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