Forum Discussion

Jeremy_Pats's avatar
Jeremy_Pats
Copper Contributor
Nov 14, 2024

Adding smallest 5 values

Good day,   I am trying to add the 5 smallest numbers in a table range of selected columns. The numbers should range between 1 and 9 meaning that 0 should not be counted if it exists in any of the ...
Formulas and Functions
m_tarler's avatar
m_tarler
Bronze Contributor
Nov 15, 2024

Are those consecutive columns? so you could use the range operator ":"?

Here are a few options:

=SUM(AGGREGATE(15,2,1/(1/Table1[@[a]:[g]]),{1,2,3,4,5}))
=SUM(TAKE(SORT(IFERROR(1/(1/Table1[@[a]:[g]]),""),,,1),,5))

note the first will five an error if there are not at least 5 non-zero values while the second will just sum the non-zero values it does find.

if the columns aren't consecutive you may need to use HSTACK() 

  • Jeremy_Pats's avatar
    Jeremy_Pats
    Copper Contributor
    Nov 15, 2024

    It is giving a #NAME error if I add HSTACK. Without HSTACK, I am only getting 0 as the answer

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 15, 2024

      As variant you may select proper columns by index, modifying m_tarler  formula

      =SUM( AGGREGATE( 15,6, 1/(1/INDEX(Table1[@[A]:[G]],1,{1,3,4,6,7,8})), {1,2,3,4,5} ) )

       

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Nov 15, 2024

        the HSTACK() should work:

        =SUM(AGGREGATE(15,2,1/(1/HSTACK([@a],[@c],[@e],[@f],[@h])),{1,2,3,4,5}))

        and in addition to Sergei's suggestion using INDEX you could use CHOOSECOLS()

        =SUM(TAKE(SORT(IFERROR(1/(1/CHOOSECOLS(Table1[@[a]:[h]],1,3,5,6,8)),""),,,1),,5))

        if you are having problems please post the actual formula you tried so we can see what might be wrong or better yet a worksheet with non-private/non-personal information.  

  • Jeremy_Pats's avatar
    Jeremy_Pats
    Copper Contributor
    Nov 15, 2024

    Thanks. Let me give it a try. They are not consecutive. Where and how to I use HSTACK()?

Resources