How to use a function to randomly generate four random numbers between 38-41 in Excel with two decimal places reserved, and the integer part cannot be repeated more than twice
How to use a function to randomly generate four random numbers between 38-41 in Excel with two decimal places reserved, and the integer part cannot be repeated more than twice
Ask online customer service
Scan the code to ask online customer service
There must be duplicates in the integer part of the four random numbers generated, but it is not allowed to have too many identical integer parts. For example, the four numbers are all 40 o'clock or three 40 o'clock. This does not work, nor can it be set, for example, what time A1 is at 38 o'clockView All
To set the cell format to "Numeric" and keep two decimal places, please follow the steps below: 1. Select the cell to format. 2. On the Home tab, in the Number group, select Currency. 3. On the Home tab, in the Format group, select Two decimal places. Next, we can use the random number generation function to generate numbers that meet the requirements.Please refer to the following formula: = RANDBETWEEN(3800, 4199)/100 This formula will generate a random number between 3800 and 4199, and divide it by 100 to get the result.For example, after entering the above formula in a cell, the system will randomly generate an integer between 0.38 and 0.42. Note that when generating random numbers, we want each number to be different and not repeated.Therefore, in practical applications, we can use the array formula RANDBETWEEN() to achieve this function.Please refer to the following example formula: = RANDBETWEEN(3800, 4199)/100 This array formula will generate an array with a length of 4, in which each element meets the requirements.For example, after entering the above formula in a cell, the system will generate four different and non repeated integers at one time. The above is a detailed explanation of how to set the cell format and use the random number generation function.I hope it is helpful to you!
A1: =RANDBETWEEN(38,41)+RANDBETWEEN(0,99)/100 A2: =MID(SUBSTITUTE(38394041,LEFT(A1,2),),RANDBETWEEN(1,3)*2-1,2)+RANDBETWEEN(0,99)/100 A3: =MID(SUBSTITUTE(SUBSTITUTE(38394041,LEFT(A1,2),),LEFT(A2,2),),RANDBETWEEN(1,2)*2-1,2)+RANDBETWEEN(0,99)/100 A4: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(38394041,LEFT(A1,2),),LEFT(A2,2),),LEFT(A3,2),)+RANDBETWEEN(0,99)/100 The above formula can produce four double digits between 38 and 41 with different integer parts, but the result is not random anymore
In Excel, you can use RAND() function to generate random numbers between 0 and 1, and then multiply by (42-38+1) to get the range of 38-41.Next, the four random numbers are sorted and the duplicate integer parts are deleted.For example:=ROUND (RAND() * (42-38+1), 2), and then use the combination of IFERROR and INDEX/MATCH to filter out non repeated integers.So we can get four numbers that meet the conditions
ZOL Q&A>How to use a function to randomly generate four random numbers between 38-41 in Excel with two decimal places reserved, and the integer part cannot be repeated more than twice