Excel Recalculate Specific Cell Random Numbers

help2017

New Member
Joined
Dec 11, 2017
Messages
5
Hi,

I'm having a problem with a specific issue and looking for help, i'm not a excel expert, so please explain in layman's terms.

Setup
I have a set of randbetween 1-100 in A1:A100, then in Column B1:B100 1 to 100 in sequential order (1,2,3,4,5,etc...), in Column C1:C100 a formula (A1*B1, A2*B2, etc...)
Having the sheet set in manual calc mode to keep the random number for re-generating.

Issue:
If i don't like the random number in A6 and would like to select that specific cell and recalculate that specific cell only and it dependencies (C6), how is this done without effecting the other randbetween calculations? Keep in mind the actual sheet is much more complicated than listed above with multiple layers of dependent cells.

Any help is appreciated.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,218
You main problem is that RANDBETWEEN is whats known as a Volatile function. It'll recalculate every time Excel performs a calculation exercise.

If you only need the number to be random once then once RANDBETWEEN has given you your figures you can Copy and Paste them as Values?

Other than that you're going to be needing a rethink it sounds like :(
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,610
Office Version
365, 2016
Platform
Windows
If you put a cell in edit mode (press F2) then hit enter the cell will recalculate and you will get a new random number. The problem is column C will not recalculate. So you could F2+enter in column A and then column C or use code to do it.

In Excel press ALT+F11 to open the VBA editor
Select insert from the menu and select module

past this code into the new module
Code:
Sub recalcell()
ActiveCell.Calculate
Cells(ActiveCell.Row, 3).Calculate
End Sub
Back in Excel press ALT+F8 to open the macro box. Select the macro and click on options.
You can then assign a short cut to the macro.

Select the cell you want to change and trigger the macro using the short cut you assigned it. The code will recalculate the active cell and the cell in column C of the same row.
 

help2017

New Member
Joined
Dec 11, 2017
Messages
5
You main problem is that RANDBETWEEN is whats known as a Volatile function. It'll recalculate every time Excel performs a calculation exercise.

If you only need the number to be random once then once RANDBETWEEN has given you your figures you can Copy and Paste them as Values?

Other than that you're going to be needing a rethink it sounds like :(

Thanks JazzSP8, was afraid of that answer. Appreciate the help.
 

help2017

New Member
Joined
Dec 11, 2017
Messages
5
If you put a cell in edit mode (press F2) then hit enter the cell will recalculate and you will get a new random number. The problem is column C will not recalculate. So you could F2+enter in column A and then column C or use code to do it.

In Excel press ALT+F11 to open the VBA editor
Select insert from the menu and select module

past this code into the new module
Code:
Sub recalcell()
ActiveCell.Calculate
Cells(ActiveCell.Row, 3).Calculate
End Sub
Back in Excel press ALT+F8 to open the macro box. Select the macro and click on options.
You can then assign a short cut to the macro.

Select the cell you want to change and trigger the macro using the short cut you assigned it. The code will recalculate the active cell and the cell in column C of the same row.

Thanks Scott, unfortunately in the actual file the dependent cells are not in the same row, they vary in rows and columns. Starting to look like, i might have to rethink the structure. Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,461
Messages
5,414,651
Members
403,541
Latest member
J0hnJ

This Week's Hot Topics

Top