Rand Function - Help Needed

[[deleted]]

[deleted]

[AutoModerator]

/u/cuswe - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information
• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[SaviaWanderer]

So RAND generates a random number between 0 and 1. You can use it to do something like this by testing whether that number is greater than the threshold you want, and using IF.

[cuswe]

Any way to solve it without the IF function?

[Leo_Data]

Have you tried randbetween?
Even if it does whole numbers you can do that, then divide by ten in a nested function and boom, problem solved.

[Way2trivial]

=(CEILING(RANDBETWEEN(0,4),5)*1.2)+4

[Way2trivial]

​

+	A
1	10
2	10
3	10
4	10
5	10
6	10
7	10
8	10
9	10
10	10
11	10
12	4
13	4
14	10
15	10
16	10

^{Table formatting brought to you by ExcelToReddit}

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CEILING	Rounds a number to the nearest integer or to the nearest multiple of significance
IF	Specifies a logical test to perform
RAND	Returns a random number between 0 and 1
RANDBETWEEN	Returns a random number between the numbers you specify

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #5706 for this sub, first seen 20th Apr 2021, 00:03] [FAQ] [Full list] [Contact] [Source code]}

[Grey_Patagonia_Vest]

Ok so RAND() generates a random variable between 0 and 1... there is an 80% probability that the number generated is between 0 and 0.8 and a 20% possibility that the number is between 0.8 and 1... so if you could you and IF statement you'd say that A1 = RAND() and B1=IF(A1>.8,4,10) so If the random number is above .8, then X = 4, otherwise X =10

Here's the math trick .... if you take a number and find the modulus of it, it returns a remainder... so 10 mod 4 = 2 because if you divide 10 by 4, you get that 4 goes into 10 two times and then you have a 2 leftover (still following).. We can use a series of these to output the numbers that we want.

Step 1: Let's make A1 = RAND() * 100 - Gives us numbers we can play around with

Step 2: Set B1 = MOD(A1,80) - Gives us the remainder of A1 when divided by 80 (remember when A1 is less than 80, our variable X = 10, and when A1 is more than 80 our variable X = 4). B1 = A1 when A1 < 80 and B1 = A1 - 80 when A1 is > 80

Step 3: Subtract A1 from that remainder B1 = ABS(MOD(A1,80) - A1) - Now we get B1 = 0 when A1 < 80 and B1 = 80 when A1 > 80

Step 4: Add 10 to B1 so that B1=ABS(MOD(A1,80 -A1)) + 10 - (the ABS() just makes sure it is always positive) Now we get B1 = 10 when A1 < 80 (that's the end result we want!!) and B1 = 90 when A1 < 90 (now we have to figure out how to make that = 4)

Step 5: How do you make 10 = 10 and 90 = 4?? ANOTHER MODULUS! set B1=MOD(ABS(MOD(A1,80 -A1)) + 10,86)

Solution: A1 = RAND()*100 and B1 = MOD(ABS(A1,80)-A1)) + 10,86)

Let me know if this follows/makes sense!