RANDBETWEEN WITH IF CONDITIONS

NAGARAJAN_EXCEL

New Member
Joined
Sep 29, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
1601379518147.png

A=RANDBETWEEN (117,151)
B=RANDBETWEEN (10.8,13.4)
C=RANDBETWEEN(D-20,D+20)
D=RANDBETWEEN(68,92)
E=C/D
F=((0.48*B*((A)/E)/1000))
G=(6700-(5*25))*F*((1/(500-25))-(1/(800-25)))*0.9

THIS FORUMULA, I have given in cells
BUT I REQUIRE
F <1.250
G>2.220
H= IF(AND(F <1.250,G>2.220),"OK","WRONG")

IS IT POSSIBLE, PLEASE GUIDE ME HOW TO DO
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
B=RANDBETWEEN (10.8,13.4)
B is 1.3
This is not between 10.8 and 13.4 so it contradicts your description

F=((0.48*B*((A)/E)/1000))
should simply be
F=0.48*B*A/E/1000

G=(6700-(5*25))*F*((1/(500-25))-(1/(800-25)))*0.9
should simply be
G=6575*F*(1/475-1/775)*0.9
 
Last edited:
Upvote 0
First, your example is "wrong" because B is 1.3, which is not between 10.8 and 13.4.

Second, is there any reason not to simplify the constant arithmetic?

For example, 6700-5*25 is 6575. 500-25 is 475, and 800-25 is 775. Moreover, 1/475 + 1/775 can be simplified, to wit: (775 - 475)/(475*775) = 300 / 368125.

Finally, what is your intention in writing RANDBETWEEN(10.8, 13.4)?

RANDBETWEEN expects integer parameters, and it returns only integers. It appears that RANDBETWEEN rounds up non-integer parameters. So B translates into RANDBETWEEN(11,14). Is that truly your intention?

If you want non-integers accurate to 1 decimal place, write RANDBETWEEN(108,134)/10. Or do want to allow for more decimal places? What about 12.34? What about 12.3456789012345?

If I have interpreted your intentions correctly and if I have done the arithmetic correctly, the following seems to show that with the correct restraints on A, B, C and D, F and G always meet your requirements without any additional constraints or checks.



Click on each cell to see formulas.

Note: In the formula for F, I substituted C/D for E, then applied an algebraic simplification, wit: 0.48*B*A/(C/D) = 0.48*B*A*D/C .

Caveat: I have not yet double-checked everything. Forgive me for any mistakes.
 
Upvote 0
ABCDEFGHI
1291.366850.780.100.5025.00WRONG

A=RANDBETWEEN (117,151)
B=RANDBETWEEN (10.8,13.4)
C=RANDBETWEEN(D-20,D+20)
D=RANDBETWEEN(68,92)
E=C/D
F=((0.48*B*(A/E)/1000))
G=(6700-(5*H))*F*((1/(500-H))-(1/(800-H)))*0.9

THIS FORUMULA, I have given in cells
BUT I REQUIRE VALUE SHOULD BE
F <1.250
G>2.220
I= IF(AND(F <1.250,G>2.220),"OK","WRONG")

NOW OK ?

NOW FOR
 
Upvote 0
sorry for wrong formula
B=RANDBETWEEN (10.8,13.4) /10


Still wrong! All you have to do is copy-and-paste the following:

=RANDBETWEEN(108, 134) / 10

Note the integers 108 and 134, not the non-integers 10.8 and 13.4. Do you understand?

You never answered my questions about your intention with that formula, just to be sure there are no other misunderstandings.

To be clear, that formula generates only values with 1 decimal place. For example, 10.8, 10.9, 11.0, 11.1 etc, and not values in between like 10.81, 10.82 etc. Do you understand?


ABCDEFGHI
1291.366850.780.100.5025.00WRONG
[....]
G=(6700-(5*H))*F*((1/(500-H))-(1/(800-H)))*0.9
[....]
NOW OK ?


No! You still have an invalid value for B in your "wrong" example. Again(!): 1.3 is not between 10.8 and 13.4. Do you understand?

But thanks for clarifying the formula for G. I suspected there was at least one other variable (H).

However, you had substituted 25 for H. So I assume that H is "constant" (invariant) for any set of random selections of A, B, C and D. That is, H is not another random variable. Right?


BUT I REQUIRE VALUE SHOULD BE
F <1.250
G>2.220


Errata.... In posting #3, I had demonstrated the calculations for min and max F as follows (modified to incorporate the reference to H):

randbtwn with constraints.xlsm
ABCDEFGHI
415113.472920.7826086956521741.2410186666666705.98470190288625025max F
511710.888681.2941176470588200.4686807272727272.26017103182590025min F
Sheet1


The min F calculation is correct. It is derived using the minimum numerator (min A, min B and min D) and the maximum denominator (i.e. max C corresponding to min D).

However, the max F calculation is incorrect.

An exhaustive search of the 968,625 combinations reveals that max F is really 1.37591200000000. That is derived using max A and max B, but min D with the corresponding min C.

In fact, there are 66 values of F >= 1.25.

So I suspect that the best way to deal with those constraints is to generate the random values in VBA, iterating until a combination of random A, B, C and D is found that is within the constraints on F and G.

I don't have time now to implement that.

But are you even amenable to a VBA solution?
 
Upvote 0
BUT I REQUIRE VALUE SHOULD BE
F <1.250
G>2.220
I suspect that the best way to deal with those constraints is to generate the random values in VBA, iterating until a combination of random A, B, C and D is found that is within the constraints on F and G.


On second thought, I believe we can avoid using VBA with the following formula for C:

=RANDBETWEEN( MAX( D1-20, ROUNDUP(0.48*B1*A1*D1/1250, 0) ), MIN( D1+20, INT(0.48*B1*A1*D1*$H$2/2220) ) )


where H1 is "H", and H2 ("hFactor") is =(6700 - 5*H1) * (1/(500-H1) - 1/(800-H1)) * 0.9 .

However, if you know H > 18.1457802973919 (+4.62E-14) is always true, we can get by with just:

=RANDBETWEEN( MAX( D1-20, ROUNDUP(0.48*B1*A1*D1/1250,0) ), D1+20 )

The MAX expression ("min C") is necessary to ensure F < 1.25.

The MIN expression ("max C") is necessary to ensure G > 2.22 . But G > 2.22 is always true for all H > 18.1457802973919 (+4.62E-14) ("min H").

Caveat: If 0.48*B1*A1*D1/1250 is an exact integer, we should use ROUNDUP(...)+1. Likewise, if 0.48*B1*A1*D1*$H$2/2220 is an exact integer, we should use INT(...)-1. I did not include those additional tests because I suspect they are never(?) true.

------

The derivation of those limits is complicated; probably TMI for most readers.

Min C is derived as follows, substituting the formulas for F and E:

F < 1.25
0.48*B*A/E / 1000 < 1.25
0.48*B*A*D / (C*1000) < 1.25
0.48*B*A*D / 1250 < C

Max C is derived as follows, substituting the formulas for G and F:

G > 2.22
hFactor * F > 2.22
hFactor * 0.48*B*A*D / (C*1000) > 2.22
hFactor * 0.48*B*A*D / 2220 > C

where hFactor is the subexpression in the formula for G, to wit:

hFactor = (6700 - 5*H) * ( 1/(500-H) - 1/(800-H) ) * 0.9

Min H to allow for the simplified RANDBETWEEN expression is derived as follows:

J1, min H: to be determined
J2, hFactor: =(6700 - 5*J1) * (1/(500-J1) - 1/(800-J1)) * 0.9
J3, min F by G: =2.22/J2
J4: min F: =0.48*B1*A1*D1 / ((D1+20)*1000)
J5, goal: =J4-J3

Solver set-up:
Set objective: J5
To value: 0
By changing: J1

Note that Solver returns an estimate, due to internal limitations. So I manually tweek the result in J1 in order to derive a more precise min H. For that purpose, I use =SUM(J4,-J3) in J5 in order to avoid dubious arithmetic tricks by Excel.

The formula in J3 is derived from G = hFactor*F > 2.22 .

The formula in J4 is based on the "min F" formula in posting #7, where A1 is min A (117), B1 is min B (10.8), D1 is min D (68), and D1+20 is the corresponding max C.
 
Upvote 0
the following formula for C:
=RANDBETWEEN( MAX( D1-20, ROUNDUP(0.48*B1*A1*D1/1250, 0) ), MIN( D1+20, INT(0.48*B1*A1*D1*$H$2/2220) ) )
[....]
Min H to allow for the simplified RANDBETWEEN expression is derived as follows:
[....]
J4: min F: =0.48*B1*A1*D1 / ((D1+20)*1000)
[....]
The formula in J4 is based on the "min F" formula in posting #7, where A1 is min A (117), B1 is min B (10.8), D1 is min D (68), and D1+20 is the corresponding max C.


Sorry for the incessant postings, but I need to clarify a point that might easily confuse the reader.

In the RANDBETWEEN formulas, A1, B1 and D1 refer to each random number, not to min A, min B and min D, which are constants.

In contrast, in the formula in J4, A1 B1 and D1 refer to the indicated constants.

In hindsight, I should have used different cell references in J4, or I should have simply used the constants.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top