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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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,101,935
Messages
5,483,779
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top