MIN() function not working on data returned from FILTER()

[Mark_Stoneking]

I'm trying to sort through data. I think what I'm trying to do is pretty simple. There's a field that has 0, 1, 2 or 3 in it. I'd like to find the minimum number that's not 0. I created a working column to turn those zeros into 8's, but MIN() only sees the 8's. I've specified the cell to be a number, but it doesn't seem to be working. I can share the file

[#2]

Hi @Mark_Stoneking,

I am not sure if this is what you are trying to achieve. Give it a look. See the file attached. I do not know why MIN doesn't work for your spilled FILTER() range. Maybe you forgot about the "#" symbol in the reference. If this doesn't answer your question please do share a file and I will investigate further.

Regards

[#3]

@Maciej_Kopczynski 

Maciej,

Thanks for responding. I tried to drag and drop my file, but it won't accept .xlsx files... any thoughts on what I'm doing wrong there?

I'm using the filter function to filter an array. I'm not sure what the # symbol is doing in your formula. If you can help me sort out my file attaching issue I'll share my file.

Thanks

[#4]

@Mark_Stoneking 

Try opening the reply box in full text editor. Then drag the file into to drop space. It should work just fine. Files with .xlsx extensions should have no restrictions.

[#5]

@Mark_Stoneking 

As an aside to the discussion, the task does not require FILTER.

= MIN(IF(numbers, numbers))

will do the job.

[#6]

Peter,
It's not that this task requires FILTER. It's that I have an array that I've already FILTERED and I don't know if that's what's causing the issue. I can create a simple list of numbers and use the MIN function and it works. It doesn't seem to work when I do it on the filtered data.

[#7]

@Mark_Stoneking 

Perhaps it would be better to try the ISNUMBER function first to check the input to MIN.  If it returns a single Boolean then the spill range is not being picked up correctly.  If it is an array but contains FALSE then there is text present despite the appearance as numbers.

[#8]

Yes! Well...no. Thanks for ISNUMBER. These are not showing up as numbers. Is there a function to convert to a number?

[#9 · Accepted solution]

@Mark_Stoneking 

The VALUE function converts a 'number' that is stored as a text string to a numerical value.  Something that puzzles me, though, is that I think MIN should coerce text to numbers where possible.

[#10]

Thanks! It works now!

[#11]

Great!