+ Reply to Thread
Results 1 to 11 of 11

Generate static random numbers within formula

  1. #1
    Registered User
    Join Date
    11-18-2014
    Location
    Uganda
    MS-Off Ver
    2013
    Posts
    25

    Generate static random numbers within formula

    Hi All,

    Issue: I have a formula here
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in which includes the RANDBETWEEN function. However, I have just noticed that it is volatile function.

    Trying to do: Would it be possible to include in the above formula somehow where a static random number is generated?

    Thanks in advance for any help given and let me know if further information is needed

  2. #2
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Generate static random numbers within formula

    Hi Steve,

    Do you mean by that once you get the result only RANDBETWEEN function's value gets converted into static random number?

    If this is the case, then as far as my knowledge, You can convert manually by pressing F9 key after selecting the highlighted part but once you hit enter you can't have function (RANDBETWEEN) with that formula.
    Please Login or Register  to view this content.
    [/COLOR]

    Or You can convert the entire formula by performing CTRL+C-->PASTE SPECIAL-->VALUE

    Waiting to hear from you.
    Thanks,
    Anil Dhawan


    Click *Add Reputation to say "Thanks" and don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) if you are satisfied.

    Don't stop when you are tired. STOP when you are done!

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,641

    Re: Generate static random numbers within formula

    Hi
    Do you want to show the CONCATENATE when random number is a specific number, i.e 100

    Try:

    Please Login or Register  to view this content.
    Quang PT

  4. #4
    Registered User
    Join Date
    11-18-2014
    Location
    Uganda
    MS-Off Ver
    2013
    Posts
    25

    Re: Generate static random numbers within formula

    hey adhawan06 - Thanks for the reply, yes this is one option I have as a last resort, but am hoping to find another way as the formula is part of a dynamic table that is constantly updated and cleared each week, so I am not looking to lose the formulae wehere possible. Although I could just have the paste option in another column (hmmmm...thanks may have to go with this option if no other way to stop the constant updating of the random numbers).

    Cheers mate

  5. #5
    Registered User
    Join Date
    11-18-2014
    Location
    Uganda
    MS-Off Ver
    2013
    Posts
    25

    Re: Generate static random numbers within formula

    Hi Bebo - If i understand you correctly, I am not looking to generate a specific random number but any random number within the CONCATENATE output that does not update whenever the workbook recalculates.

    Sorry if I may not have been clear, the current formula output result is = Student davids-steve-222088
    However, when excel recalculates automatically, it will change the Student davids-steve-222088 to something random again. This is what I am trying to hope get asisstance on, to stop this from happening if at all possible.

  6. #6
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Generate static random numbers within formula

    Hi Steve,

    Yeah there is some limitation in excel too. :P

    Once you convert your formula into static value, you can't get it back...And I can understand no one would love to loose formula so better you have options to paste the entire column in some other column and then can go for PASTESPECIAL-VALUE method as this one is left at last.





    If your query has been solved, please take few seconds marking this thread as SOLVED and say thanks to the person by adding *Add Reputation.

  7. #7
    Registered User
    Join Date
    11-18-2014
    Location
    Uganda
    MS-Off Ver
    2013
    Posts
    25

    Re: Generate static random numbers within formula

    adhawan06 - Yep you're right about that but I may just leave this post open for a day and see what comes back, may get another way to do what I am trying to achieve here, nonetheless cheers for the help

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Generate static random numbers within formula

    Why don't you try something like this

    Set cell A1 to a toggle switch (ie you want the formula to update when A1 = YES) then change your formula to this

    =if(A1="YES",IF(AND(B3="",C3="",D3=""),"",CONCATENATE("Student ",B3,"-",C3,"-",RANDBETWEEN(0,500000))),z1)

    where Z1 is the cell reference you have this formula in. it will recalculate when you want it to (when A1 = Yes) and when you change A1 to anything but YES it will give you a circular reference warning the first time but only the first time but more importantly it won't change your number
    Happy with my advice? Click on the * reputation button below

  9. #9
    Registered User
    Join Date
    11-18-2014
    Location
    Uganda
    MS-Off Ver
    2013
    Posts
    25

    Re: Generate static random numbers within formula

    Crooza - Thanks for this. Your suggestion achieves extaly what I have been after.

    Appreciate it

    P.s - will i get any problems having circular references within a dynamic table? (perhaps I should post this in a new thread )

  10. #10
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Generate static random numbers within formula

    P.s - will i get any problems having circular references within a dynamic table? (perhaps I should post this in a new thread )
    Any values linked to the circular reference will remain the same (from testing I've done). Any values not linked to the circular reference will recalculate

  11. #11
    Registered User
    Join Date
    11-18-2014
    Location
    Uganda
    MS-Off Ver
    2013
    Posts
    25

    Re: Generate static random numbers within formula

    Quote Originally Posted by Crooza View Post
    Any values linked to the circular reference will remain the same (from testing I've done). Any values not linked to the circular reference will recalculate
    Cheers mate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Random Numbers assigned to static values multiple times no duplicates....?
    By rhattala in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-24-2014, 09:40 PM
  2. Print to text file and Static random numbers?
    By xizor in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-10-2014, 03:58 PM
  3. Print to text file and Static random numbers.
    By xizor in forum Excel General
    Replies: 0
    Last Post: 06-28-2014, 10:46 PM
  4. Formula to generate unique random numbers?!?
    By aims in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2013, 03:14 PM
  5. Replies: 2
    Last Post: 09-24-2010, 12:11 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1