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 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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!