-1

The number needs to have a length of 12 and 2 decimal places.

Probably there's a solution with "=RAND()".

Sarah
  • 21
  • 4
  • Please can you be more specific about the length. Is the total length to be 12 (including the decimal point) or just the whole-number part? Would you allow leading zeros? Is there a minimum/maximum? What about two trailing zeros as a decimal part? – JvdV Mar 13 '23 at 11:57
  • Sure. 12 is the total length, including the decimal places. I would not allow leading zeros, so the number would be between 1000000000,00 and 9999999999,99. Maybe something like this: =ROUNDUP(RAND()*9999999999;2) But this gives me a random number between 0,00 and 9999999999,99. – Sarah Mar 13 '23 at 12:09
  • 1
    [RANDBETWEEN](https://support.microsoft.com/en-us/office/randbetween-function-4cc7f0d1-87dc-4eb7-987f-a469ab381685)? – Ocaso Protal Mar 13 '23 at 12:16

4 Answers4

3

the number would be between 1000000000,00 and 9999999999,99

You can do:

=RANDBETWEEN(1000000000;9999999999)+ROUND(RAND();2)

Because you are working with numbers pretty huge, remember to format the cell as numeric with 2 decimals or you won't see the result!:

enter image description here

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • The `+RAND()` does not limit it to 2 decimal places. Formatting it as 2dp doesn't remove the decimal places, it just hides them. That may not matter to the OP, but thought it might be important to note. – Gravitate Mar 13 '23 at 13:06
  • @Gravitate You are right, RAND() should be inside a ROUND – Foxfire And Burns And Burns Mar 13 '23 at 13:10
  • 1
    Yeah... I just thought of an extreme edge case where the `RANDBETWEEN` returned 9999999999 and the `RAND()` produced 0.999, which would result in the displayed number being `10000000000.00` which has a total length of 13. Extremely unlikely I know... – Gravitate Mar 13 '23 at 13:14
  • 2
    A less verbose equivalent could be `=RANDBETWEEN(10^9,10^10-1)+ROUND(RAND(),2)` – JvdV Mar 13 '23 at 13:43
3

Lots of answers, but here is my solution:

=RANDBETWEEN(1000000000,9999999999)+RANDBETWEEN(1,99)/100

This formula will produce a random number and also a random decimal place, decimal numbers produce by dividing the decimal place numbers by 100 and adding them to the whole number.

Make sure you format your cells as number with 2 decimal places.

5202456
  • 966
  • 14
  • 24
2

This is definitely NOT the best way to do it. It is not efficient and there will be far better mathematical approaches but it does work and does guarantee all your requirements. It is also very easy to see what it is doing and adjust if needed.

=CONCAT(
    RANDBETWEEN(1000000000,9999999999),
    ".", 
    RANDBETWEEN(0,99)
)+0

This constructs the number as a string from two random integers before and after the decimal place. The +0 simply ensures that Excel treats the result as a number rather than text.

Gravitate
  • 2,885
  • 2
  • 21
  • 37
  • 1
    This handles all the cases. I'm just wondering why the CONCAT? i think it would be better to sum up both rands and return a numeric output. Anyways, upvoted because it handles all the cases. – Foxfire And Burns And Burns Mar 13 '23 at 13:17
  • Yeah, you are right. But I didn't think of that until after you had posted your answer, and if I changed it now, they would essentially be identical. There is an argument that this is easier to understand what it is doing. But yours will certainly be more efficient and should be preferred. Especially if there are a large number of them to be calculated. – Gravitate Mar 13 '23 at 13:30
1

Is it necessary to generate the decimal part separately from the integer part? Maybe you can do this:

=RANDBETWEEN(10^10,10^11-1)/100

formatted as a number (if you want the total length to be 12 including the decimal point).

As suggested by @Gravitate, if you need the length to be twelve not including the decimal point, it would be

=RANDBETWEEN(10^11,10^12-1)/100

Or in general

=LET(n,12,d,2,RANDBETWEEN(10^(n-1),10^n-1)/10^d)

where n is the number of digits excluding the decimal point and d is the number of decimal digits.

But you would need to adjust the format manually or use this to get it to display correctly:

=LET(n,12,d,2,TEXT(RANDBETWEEN(10^(n-1),10^n-1)/10^d,"0."&REPT("0",d)))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • This is a great solution but I think the OP wanted a length of 12, excluding the decimal point. So shouldn't it be `=RANDBETWEEN(10^11,10^12-1)/100`? – Gravitate Mar 13 '23 at 15:36
  • Yes, thanks, I wasn't sure about that, but will add it as an alternative. – Tom Sharpe Mar 13 '23 at 17:45