Unusual shortcuts in Excel

[Dan Wilson]

Good day. I am using Excel out of Office 365 (updated) and Windows 10 Home (updated). I have a workbook that is using quite a few RANDOM functions. I know that the F9 key actuates the Random feature. What I have recently discovered is that the Delete key on my keyboard also triggers the Random functions. I have read the Help file on Keyboard Shortcuts but cannot find anything that describes this feature. Could this be something that was accidentally created in Excel, or in a feature carried over from Office365? The Delete key works exactly the same as the F9 key. I appreciate any help with this.
Thank you,
Dan Wilson...

 

[Dan Wilson]

I also just discovered something else. I use Cell A23 to contain a word that I wanted to find in my music folder. I then added

=CONCATENATE("*",A23,"*")

to Cell B21 to add the necessary punctuation to my search word. The formula works and places the search word in B21. Upon entering the "Enter" key on my keyboard after entering the search word, all of the adjacent Random formulas computed new numbers in the remainder of the worksheet. This is very strange.

 

[Peter_SSs]

This is very strange.

It isn't strange to me. ' Random' functions (I presume you mean functions like RAND(), RANDBETWEEN(), RANDARRAY()) are volatile functions
Lots of things trigger recalculation of volatile functions, it was never just F9. Have a good look through the link above and you will see many actions that trigger a recalculation of volatile functions.

 

[Dan Wilson]

Hello again Peter_SSs and thank you for responding. Wow! That's quite a bit of information. I copied it to a Word Document so that I can review it until I understand it. I am certainly glad it's not something that I caused. Over the last 20 years plus, I feel that I have learned quite a bit about Excel with the help of responders like yourself, but obviously I have a long way to go. This will certainly alter the way that I use the Random functions.
Ooops! I just proved something. After examining the numbers on the worksheet containing the Random functions, I examined a different worksheet in the same workbook, ran a Macro on that worksheet and then went back to the worksheet with the Random functions. All of the Random results had changed meaning that every time I modify anything in the workbook (contains 4 worksheets), the Random numbers will change. That is not going to work. The obvious solution is to create an entirely new workbook with only the Random functions or print out the results of a current Random creation.
Thank you again for your help,
Dan Wilson...

 

[Scott Huish]

F9 isn't specifically "actuating" random functions, it's doing that because F9 is the shortcut key to force the sheet to recalculate.

 

[Peter_SSs]

.. ran a Macro on that worksheet ..

That macro must have caused something in the workbook to recalculate. When that happens, any volatile functions in the workbook will also recalculate. That is explained in the first sentence of the link I gave you.

A Volatile Function is one that causes recalculation of the formula in the cell where it resides every time Excel recalculates.

meaning that every time I modify anything in the workbook (contains 4 worksheets), the Random numbers will change.

That isn't necessarily true. A macro could modify some things in the workbook without triggering a recalculation.

If you are having problems with the random values changing and you already have one or more macros in the workbook, why not have the relevant macro (or a new one if required) generate the random data and enter it in the workbook as a fixed value then you wouldn't have the problem? Next time the macro runs it would generate new random data.

 

[Dan Wilson]

Good day again Peter_SSs and thank you for the follow-up. The Macros mentioned do severe sorting and manipulation of the data in other worksheets of the workbook. I understand now what is going on thanks to your explanations and the Volatile Functions explanation. I expect that I will require use of the Random function only once a week. Now that I know what is happening, I will exercise the Random function and then print out the results before accessing any other data in the workbook. I sincerely appreciate the follow-up.