Forum Discussion

Xostensial's avatar
Xostensial
Copper Contributor
Jun 01, 2024

Need help excluding values from INDEX function

I'm a complete newbie to excel and am struggling to find a way to get a random row from B1 - B15 while excluding the rows with 0.

 It's important to note that I need this formula to be able to work when the 0 and other values are in different positions, and when there are a different number of 0 values in the column, but the total amount of numbers will always be 15. Thanks so much for the consideration!

excel
Formulas and Functions

2 Replies

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Jun 01, 2024

    Xostensial 

     

     

     

    =IFERROR(INDEX(FILTER(B1:B15,B1:B15<>0),SORTBY(SEQUENCE(15),RANDARRAY(15))),"")

     

    And without gaps:

    =IFERROR(INDEX(FILTER(B1:B15,B1:B15<>0),SORTBY(SEQUENCE(COUNTIF(B1:B15,"<>0")),RANDARRAY(COUNTIF(B1:B15,"<>0")))),"")

     

    That should work!

     

    • Xostensial's avatar
      Xostensial
      Copper Contributor
      Jun 02, 2024

      dscheikey Didn't work exactly how I wanted it to, but to be fair I wasn't very precise with my wording, and your response helped me find a workaround! Thanks for your time!

Resources