Forum Discussion
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 cells. This is the formula I have been using but I am not getting a satisfactory answer:
=IFERROR(SUM(SMALL(IF(([@[L.PNT]], [@[F.PNT]], [@[M.PNT]], [@[CS.PNT]], [@[B.PNT]], [@[CE.PNT]], [@[G.PNT]], [@[H.PNT]], [@[RE.PNT]], [@[DT.PNT]], [@[A.PNT]], [@[CO.PNT]], [@[SC.PNT]])<>0, ([@[L.PNT]], [@[F.PNT]], [@[M.PNT]], [@[CS.PNT]], [@[B.PNT]], [@[CE.PNT]], [@[G.PNT]], [@[H.PNT]], [@[RE.PNT]], [@[DT.PNT]], [@[A.PNT]], [@[CO.PNT]], [@[SC.PNT]])), {1,2,3,4,5})),0)
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()
Thanks. Let me give it a try. They are not consecutive. Where and how to I use HSTACK()?
It is giving a #NAME error if I add HSTACK. Without HSTACK, I am only getting 0 as the answer
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} ) )
