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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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,616
Office Version
  1. 365
  2. 2016
Platform
  1. 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,127,065
Messages
5,622,475
Members
415,897
Latest member
Barry18180

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

Which adblocker are you using?

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
Top