+ Reply to Thread
Results 1 to 10 of 10

Substitute & get randbetween from cell reference

  1. #1
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,171

    Substitute & get randbetween from cell reference

    Hello guys,
    I have the randbetween value in a cell and the multiple value in the other cell. I want to get values with the help of randbetween formula in cells H2 to H15. I want the formula to select the randbetween amount from the cell B2 and the multiples value from the cell C2. How do I combine the above 2 formulas into one and get the result?
    If , in the B column, I write the value as 6-10, it is not accepting in the general format. Since, multiples is used in most of the cases, I would like to know how to enter 6-10 or directly 6,10 which also is not accepted by excel in the required format. Something like this '=RANDBETWEEN(SUBSTITUTE(B2,"-",")*C2 but it ain't working.
    Last edited by RAJESH SHAH; 09-25-2022 at 05:08 PM. Reason: #Solved by HansDouwe

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,882

    Re: Substitute & get randbetween from cell reference

    This ??

    =RANDBETWEEN(--LEFT($B2,FIND("-",$B2)-1),--MID($B2,FIND("-",$B2)+1,255))*C2

    OR

    =RANDBETWEEN(--MID($B2,1,FIND("-",$B2)-1),--MID($B2,FIND("-",$B2)+1,255))*C2
    Last edited by JohnTopley; 09-25-2022 at 02:20 PM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,171

    Re: Substitute & get randbetween from cell reference

    Hello John Topey. Except the first cell, I am getting a value error in the all cells from H3 to H15

  4. #4
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,171

    Re: Substitute & get randbetween from cell reference

    Got it right now. I freezed all the cells and it worked. =RANDBETWEEN(--MID($B$2,1,FIND("-",$B$2)-1),--MID($B$2,FIND("-",$B$2)+1,255))*$C$2.
    Thanks Man.

  5. #5
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,171

    Re: Substitute & get randbetween from cell reference

    Can you please also tell me the solution for the second problem." I am not able to enter the range between 6-10 in any cell. It gets converted into a date by default. At times, I need the range between 60-100 in multiples of 10 like 60,70,80,90,100 but if enter 6-10 it is not displayed in that format.
    Last edited by RAJESH SHAH; 09-25-2022 at 02:53 PM.

  6. #6
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Substitute & get randbetween from cell reference

    An option:
    Use 2 seperate fields for the range and try this formula (copy down and across):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,171

    Re: Substitute & get randbetween from cell reference

    Using 2 separate fields is a good idea HansDouwe. But when I enter the range as 6 and 10 in place of 60 and 100 I am getting the result as 10 in all the columns. I need random figures between 60 to 100 like 60, 70, 80, 90 and 100. Actually it is a very good idea. I added a few more valued in rows 5,6 and 7 and extended the columns to 5,6 and 7 to get the result. I can auto extract values of any number of rows at once. The only problem is that I should not get the same figures in all the rows in a single column.
    Last edited by RAJESH SHAH; 09-25-2022 at 03:15 PM.

  8. #8
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Substitute & get randbetween from cell reference

    Indeed if you enter the range between 6 and 10 and everything should be a multiple 10, you only get 10's because in the range 6-10 is only 10 a multiple of 10.
    If you need random figures between 60 to 100 that should be a multiple of 10, please try range between 60 and 100 (or between 55 and 104) and try multiple 10.

  9. #9
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,171

    Re: Substitute & get randbetween from cell reference

    I understood now. Thanks HansDouwe.

  10. #10
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Substitute & get randbetween from cell reference

    I'm glad to hear it works. Thx for the feedback and rep .

+ 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. [SOLVED] Substitute reference in cell formula with another reference.
    By harpbelle in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-17-2021, 01:03 AM
  2. [SOLVED] got a RandBetween that uses a cell to reference but I need the value to be put in it
    By Michael Island in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-20-2020, 06:59 AM
  3. [SOLVED] How to replace or substitute function formulas but left the cell reference intact?
    By Franky alta in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-02-2016, 12:48 AM
  4. Replies: 10
    Last Post: 07-03-2015, 04:29 PM
  5. [SOLVED] Can i substitute using a cell reference?
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-25-2013, 03:17 PM
  6. How to substitute dates using cell reference in SQL query using VBA
    By labman39 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-29-2012, 01:07 AM
  7. Replies: 11
    Last Post: 02-22-2006, 09:20 AM

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