Forum Discussion

Mark_Stoneking's avatar
Mark_Stoneking
Copper Contributor
Feb 19, 2024

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

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

    • Mark_Stoneking's avatar
      Mark_Stoneking
      Copper 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.
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor

        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.

  • 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

    • Mark_Stoneking's avatar
      Mark_Stoneking
      Copper Contributor

      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

       

      • Maciej_Kopczynski's avatar
        Maciej_Kopczynski
        Brass Contributor

        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.