# 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?

Tags:

• ✭✭✭✭✭✭

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))`

• ✭✭✭✭✭✭

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 2-4, 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!