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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,010
Messages
5,466,011
Members
406,460
Latest member
tryingtogoalone

This Week's Hot Topics

Top