Forum Discussion

cl1234's avatar
cl1234
Copper Contributor
Aug 21, 2021

Have a cell recalculate

I'm having an issue in excel. Lets say I have a cell choose a random number, between 1 and 100, but if it chooses one specific number, something like 20, it will choose a different random number until it spits out something other than 20.

 

In my real example, 20 is variable.

 

Is there any way to do this?

 

 

excel
Formulas and Functions
Macros and VBA

4 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 22, 2021

    cl1234 

    Using Excel 365 Insider beta there are things called Lambda functions.

    = RANDλ(20)

where RANDλ is defined by

= LAMBDA(exclude,
     LET(
       r, RANDBETWEEN(18,22),
       IF(r=exclude,RANDλ(exclude),r)
     )
   )

    This is not for the fainthearted!

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Aug 22, 2021
        Very unlikely. Do you?
        At present, many Excel users would not even recognise my solutions to their problems as being Excel, never mind whether they are correct. All I might hope to do is dent the complacency that assumes that what was done in the past is the only way!
  • szilvia_vf's avatar
    szilvia_vf
    Brass Contributor
    Aug 22, 2021
    how about simply creating a list without 20, and making the cell pickung up a value from the list?
    in this case you would have let's say 1,2,3...19,21,22,...100 in cells A1:A99, and formula could be =INDEX(A1:A99,randbetween(1,99))
    in case the missing number varies, this won't be a solution, but if it is a fix list, then the workaround would surely do.