Forum Discussion
TEXTJOIN with MIN when min is zero
Hi,
I am using TEXTJOIN to show all items that have the most and least quantities available, but if I have no items (zero quantity), the formula returns an error.
How can I return the text when the minimum is zero?
=TEXTJOIN(" - ",TRUE,IF(C:C=MIN(C:C),B:B,""))
Thanks.
First of all, the TRUE argument has no purpose here since you are returning an array with TRUE and FALSE in the IF statement. When the minimum is zero it will return over a million FALSE's as all empty cells also evaluate to TRUE in IF(C:C=MIN(C:C),B:B,"")
Then, TEXTJOIN will attempt to join 0-shoe-0-0- etc. (over a million zeroes to follow). But that exceeds the length of text a cell can hold. Hence and error.
Avoid referencing whole columns. It will work if you reference the exact range that holds the Item and Number data. This will work:
=TEXTJOIN(" - ",,IF(C2:C8=MIN(C2:C8),B2:B8,""))
Or, if you really want to reference entire columns do this:
=TEXTJOIN(" - ",,IF((C:C<>"")*(C:C=MIN(C:C)),B:B,""))
First of all, the TRUE argument has no purpose here since you are returning an array with TRUE and FALSE in the IF statement. When the minimum is zero it will return over a million FALSE's as all empty cells also evaluate to TRUE in IF(C:C=MIN(C:C),B:B,"")
Then, TEXTJOIN will attempt to join 0-shoe-0-0- etc. (over a million zeroes to follow). But that exceeds the length of text a cell can hold. Hence and error.
Avoid referencing whole columns. It will work if you reference the exact range that holds the Item and Number data. This will work:
=TEXTJOIN(" - ",,IF(C2:C8=MIN(C2:C8),B2:B8,""))
Or, if you really want to reference entire columns do this:
=TEXTJOIN(" - ",,IF((C:C<>"")*(C:C=MIN(C:C)),B:B,""))
The use of the full column was only because the data will be updated by someone else, but I understood the concept you explained.
It worked very well, thank you very much.
