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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,187
Messages
5,509,707
Members
408,750
Latest member
vdspharma

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top