Forum Discussion
Mark_Stoneking
Feb 19, 2024Copper Contributor
MIN() function not working on data returned from FILTER()
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 col...
- Feb 19, 2024
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.
PeterBartholomew1
Feb 19, 2024Silver Contributor
As an aside to the discussion, the task does not require FILTER.
= MIN(IF(numbers, numbers))
will do the job.
Mark_Stoneking
Feb 19, 2024Copper Contributor
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.
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.
- PeterBartholomew1Feb 19, 2024Silver Contributor
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.
- Mark_StonekingFeb 19, 2024Copper ContributorYes! Well...no. Thanks for ISNUMBER. These are not showing up as numbers. Is there a function to convert to a number?
- PeterBartholomew1Feb 19, 2024Silver Contributor
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.