How to ignore (0.00) when using SMALL Formula
I have 3 columns and each row I would like to return the smallest number. If one of the three columns contains a zero then I would like to return the NEXT smallest  essentially ignore the zero (0.00). How do I accomplish this?
Best Answer

Min Column2 formula:
=MIN(COLLECT([Column2]:[Column2], [Column2]:[Column2], >0))
Min Column3 formula:
=MIN(COLLECT([Column3]:[Column3], [Column3]:[Column3], >0))
Min Column4 formula:
=MIN(COLLECT([Column4]:[Column4], [Column4]:[Column4], >0))
Answers

Min Column2 formula:
=MIN(COLLECT([Column2]:[Column2], [Column2]:[Column2], >0))
Min Column3 formula:
=MIN(COLLECT([Column3]:[Column3], [Column3]:[Column3], >0))
Min Column4 formula:
=MIN(COLLECT([Column4]:[Column4], [Column4]:[Column4], >0))

@Mike TV THANK YOU !!!!!!

Another question resulting from figuring the above out. Is there a way I can automatically visually illustrate the columns used in the 'Grand Total'? I.E., which of the set of 3 columns plus the next set of 4 columns was the 'Min' that was used in the the final calcs.
I need to illustrate the total # of $383,977.46 is actually $495,772.00$0.00$89,022.46$30,750.00)  I'm looking for a function or automation that would possibly highlight the cells or insert the actual formula (as text) into another column.

You could use a hidden helper column that has a MATCH function in it to output the column number then use conditional formatting to say that if the helper column equals one, highlight the first column.
=MATCH([Min Column Name]@row, [1st Column]@row:[Last Column]@row, 0)

@Mike TV , from the above example from @PeggyLang let's say I wanted the lowest value from Columns 24, excluding any zero's or blanks. What would that formula look like?
I have tried the following so far, but no luck:
IFERROR(MIN([Column2]@row :[Column4]@row ))  this yields an #unparseable
IFERROR(MIN([Column2]@row, [Column3]@row , [Column4]@row ))  this yields an #incorrect argument set
Any suggestions on what I am doing wrong? thank you.

@lmlearning You would use a MIN/COLLECT combo.

@Paul Newcome while I was waiting for a reply I attempted =MIN(COLLECT([Column2]@row, [Column3]@row , [Column4]@row)) but it did not exclude the zero, the columns had 94, 0, 76 and it returned the zero as the lowest value.
I tried =MIN(COLLECT([Column2]@row, [Column3]@row , [Column4]@row, @cell <> "")) but that returned Invalid operation.

The syntax is off.
=MIN(COLLECT([Column1]@row:[Column3]@row, [Column1]@row:[Column3]@row, @cell > 0))

@Paul Newcome it worked! Thank you very much!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 46 Industry Talk
 387 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!