RANDBETWEEN Two decimal numbers to produced a decimal number

[Taconumber]

Howdy,

I am stuck on a formula and am hoping someone here can help me.

Essentially, i am doing a tolerance sheet. Showing the design value, the actual value and the difference between the two. There is a tolerance of +0.015 and -0.000. So if the difference falls out of this range i need it to randomly generate a number that is between the design value and the design tolerance.

I have come up with this. =IF(C7>$B$2,RANDBETWEEN(A7-$B$3,A7+$B$2) BUT it only generates a integer..

and if i use the formula =IF(C7>$B$2,RANDBETWEEN(A7-$B$3,A7+$B$2)+RAND()) It generates a value that is out of the tolerance range because it generates the RANDBETWEEN and adds the RAND whch returns a decimal.

I have attached a example work book.

tolerance workbook.xlsx
	A	B	C	D	E	F	G	H	I
1
2	Upper Tolerance	0.015
3	Lower Tolerance	0.000
4
5			DIFF
6	456.983	457.017	0.034			457.000	FALSE		456.998
7	456.897	456.883	-0.014			FALSE	457.993
8	456.955	456.942	-0.013			FALSE	457.054
9	456.888	456.923	0.035			457.722	FALSE
Sheet1

Cell Formulas
Range	Formula
F6	F6	=IF(C6>$B$2,RANDBETWEEN(A6,I6))
G6:G9	G6	=IF(C6<$B$3,RANDBETWEEN(A7-$B$3,A7+$B$2)+RAND())
F7:F9	F7	=IF(C7>$B$2,RANDBETWEEN(A7-$B$3,A7+$B$2)+RAND())
I6	I6	=A6+$B$2
C6:C9	C6	=B6-A6

 

[DanteAmor]

Try this:

varios 18oct2022.xlsm
	A	B	C	D	E	F
1
2	Upper Tolerance	0.015
3	Lower Tolerance	0
4
5			DIFF
6	456.983	457.017	0.034			456.996
7	456.897	456.883	-0.014			FALSO
8	456.955	456.942	-0.013			FALSO
9	456.888	456.923	0.035			456.893
Hoja2

Cell Formulas
Range	Formula
C6:C9	C6	=B6-A6
F6:F9	F6	=IF(C6>$B$2,(RANDBETWEEN(A6*1000,(A6+$B$2)*1000))/1000)

I hope with that example you can get the value in case the difference is less than 0. Otherwise explain what you need when the difference is less than 0.

 

[bebo021999]

Try to multiply both with 1000 to get random integer first, then afterall divide back to 1000

=IF(C6>$B$2,RANDBETWEEN((A6-$B$3)*1000,(A6+$B$2)*1000)/1000)

 

[Taconumber]

They both work great! I was trying to think logically but it appears you need some creativity with this formula.

Try to multiply both with 1000 to get random integer first, then afterall divide back to 1000

=IF(C6>$B$2,RANDBETWEEN((A6-$B$3)*1000,(A6+$B$2)*1000)/1000)

Try this:

varios 18oct2022.xlsm
	A	B	C	D	E	F
1
2	Upper Tolerance	0.015
3	Lower Tolerance	0
4
5			DIFF
6	456.983	457.017	0.034			456.996
7	456.897	456.883	-0.014			FALSO
8	456.955	456.942	-0.013			FALSO
9	456.888	456.923	0.035			456.893
Hoja2

Cell Formulas
Range	Formula
C6:C9	C6	=B6-A6
F6:F9	F6	=IF(C6>$B$2,(RANDBETWEEN(A6*1000,(A6+$B$2)*1000))/1000)

I hope with that example you can get the value in case the difference is less than 0. Otherwise explain what you need when the difference is less than 0.

They both work great! I was trying to think logically but it appears you need some creativity with this formula.

Thanks for the help. It is much appreciated.

 

[SMK041]

Just to build on what others have posted, the randbetween function will only return an integer.

There is another function "=rand()" that will return decimals to however plant places you have the cell formatted to. You can also round the function to 3 places if you wanted to fix it.

So another way to complete the task is to use the rand() function to add a random decimal.

The syntax to build this function out further is =rand()*lower*upper.