Need some help on coming up with random number from 2 excel columns.

webuxer

New Member
Joined
Nov 6, 2013
Messages
8
Hi, I need some help in coming up with some random numbers from 2 columns in an excel file.


If you see on the attached file on column F and G there are some numbers. I need to come up with some random numbers and put them in column H. But the number from column F needs to be a few numbers higher (sometimes a number up, or 2 numbers up, up to three numbers up, but they need to be random throughout the whole column, (like I said sometimes 1 or 2 or 3 numbers up). On column G is the same but the numbers need to go down by 1 or 2 or 3 numbers, just random numbers. On the results on the H column the left number needs to be smaller than the right number. If you see on the attached file the first result in column H .428 is lower than .440 (The number on the left needs to smaller. I'm really trying to come out with a formula but I cant figure it out. Its a long list maybe someone can help me on this if is even possible.

Thanks in advanced
 

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
Does this work for you?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">0.427</td><td style="text-align: right;;">0.447</td><td style=";">0.429 - 0.445</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0.205</td><td style="text-align: right;;">0.225</td><td style=";">0.208 - 0.224</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">0.053</td><td style="text-align: right;;">0.073</td><td style=";">0.056 - 0.07</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">0.053</td><td style="text-align: right;;">0.073</td><td style=";">0.055 - 0.07</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">0.05</td><td style="text-align: right;;">0.07</td><td style=";">0.052 - 0.068</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">randb</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H1</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,3</font>)/1000+F1&" - "&-RANDBETWEEN(<font color="Blue">1,3</font>)/1000+G1</td></tr></tbody></table></td></tr></table><br />
 

webuxer

New Member
Joined
Nov 6, 2013
Messages
8
Does this work for you?

Excel 2010
FGH
10.4270.4470.429 - 0.445
20.2050.2250.208 - 0.224
30.0530.0730.056 - 0.07
40.0530.0730.055 - 0.07
50.050.070.052 - 0.068

<tbody>
</tbody>
randb

Worksheet Formulas
CellFormula
H1=RANDBETWEEN(1,3)/1000+F1&" - "&-RANDBETWEEN(1,3)/1000+G1

<tbody>
</tbody>

<tbody>
</tbody>
Thank you soo much this is what i needed, can it be possible to be able to have a number on your sample
on Row 3, the result for 0.073, instead of 0.07 be something like 0.071. I would like to keep the same amount of numbers.
Thanks

<table cellpadding="2.5px" rules="all" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;="" border:="" 1px="" solid="" rgb(187,="" 187,="" 187);="" border-collapse:="" collapse;"="" width=""><tbody></tbody></table>
 
Last edited:

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
Try this:

=TEXT(RANDBETWEEN(1,3)/1000+F1,"0.000")&" - "&TEXT(-RANDBETWEEN(1,3)/1000+G1,"0.000")
 

webuxer

New Member
Joined
Nov 6, 2013
Messages
8
Thanks you so much for your help, you have answered my question. Thanks for your time. I have another question but I will put it in another thread.
Thanks
 

Forum statistics

Threads
1,082,342
Messages
5,364,783
Members
400,815
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top