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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,970
Office Version
  1. 2010
Platform
  1. Windows
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.
 

NAGARAJAN_EXCEL

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

ADVERTISEMENT

sorry for wrong formula

B=RANDBETWEEN (10.8,13.4) /10
 

NAGARAJAN_EXCEL

New Member
Joined
Sep 29, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,970
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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?
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,970
Office Version
  1. 2010
Platform
  1. Windows
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.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,970
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,483
Members
414,146
Latest member
marginmakerb

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
Top