# how to conditionally 'freeze' the value in a cell?

#### excelquest23

##### Board Regular
Hello,

I would like for the value in cell A1 to be randomly drawn between 5 and 10 if the value of A2 is not 1, and to be 'frozen' with the existing value in A1 if A2 is equal to 1.

So, if A2 has a value 2 then value in A1 could be 8.

If value of A2 is then changed to 1, I would like for value in A1 to remain 8.

Is there an easy way to do this with an IF command? I tried the following but it didn't work:
=IF(A2=1,A1,(RANDBETWEEN(5,10)))

Thanks!

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Tom Urtis

##### MrExcel MVP
Explain how the value in A2 changes:

By manual entry?
By formula?
By a userform command?
Some other way?

Also explain what "between 5 and 10" means to you:

6, 7, 8, 9 (literal meaning)
or
5, 6, 7, 8, 9, 10 (maybe you meant this)
or something else, especially if you want decimals, example 6.9

Last edited:

#### excelquest23

##### Board Regular
Thanks for your response. The value in A2 changes randomly between 0 and 1.

Thanks.

#### Tom Urtis

##### MrExcel MVP
Please read my question for the first time or again, I asked HOW the value changes.

Also see my second edited question.

#### excelquest23

##### Board Regular
A2 changes by manual entry.

I meant the literal meaning (6,7,8,9) for "between 5 and 10"

Thanks

#### Tom Urtis

##### MrExcel MVP
For the worksheet on which this is happening, right click on that sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address <> "\$A\$2" Or .Cells.count > 1 Then Exit Sub
If .Value = 1 Then Exit Sub
.Offset(-1).Value = Int(Rnd() * 4 + 6)
End With
End Sub``````

Keep in mind...
If you enter a zero (0) or any number other than 1, or you hit the Delete key, and you see no change in cell A1, it will not mean the code is not working, it will mean that the random number selected happens to be the same number that was in there before, which is going to happen roughly 25% of the time because you only have a possible pool of 4 numbers that can be randomly displayed.

The above code can be modified for stuff like that, but because you did not specify for it, I did not include it.

#### excelquest23

##### Board Regular
Great. Thank you very much!

Replies
13
Views
216
Replies
6
Views
251
Replies
6
Views
125
Replies
2
Views
101
Replies
2
Views
91

Threads
1,191,670
Messages
5,987,951
Members
440,121
Latest member
eravella

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back