How to ignore (0.00) when using SMALL Formula

PeggyLang

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?

Mike TV

@PeggyLang

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

@PeggyLang

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

PeggyLang

@Mike TV THANK YOU !!!!!!

PeggyLang

@Mike TV

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.

Paul Newcome

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)

lmlearning

@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.

Paul Newcome

@lmlearning You would use a MIN/COLLECT combo.

lmlearning

@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.

Paul Newcome

The syntax is off.

=MIN(COLLECT([Column1]@row:[Column3]@row, [Column1]@row:[Column3]@row, @cell > 0))

lmlearning

@Paul Newcome it worked! Thank you very much!