RANDBETWEEN Broken

Question

This one is dumbfounding me...

I started with this:

Cell 1: =randbetween(1, 45)

For many reasons, I switched to a few named cells, and wound up with this:

Cell 1: =randbetween(1, namedCell)

namedCell: 45 (not a function)

I have other named cells, including a several other namedCellOthers with the following results:

Cell Others: =randbetween(1,namedCellOthers)

namedCellOthers: function derived 31

I ran function audits for both the Trace Precedents and the Trace Dependents.  Everything relates correctly. 

In the end, I get two sets of calculation errors.

First, I see no results of the randbetween (the 1 to 45 versions) over 31.  I reset the values to 100000, and still saw no results over 31.

Second, when 1 is displayed, it appears to have 2 actual values, 1 and 32.  I have a downstream dependent function (in a different cell) that gives results if randbetween == 1.  When the value of RANDBETWEEN appears to be 1, but I don't get a result from the downstream dependent function, I ran a Evaluate Formula Audit on the downstream dependent formula.  With that audit (and the evaluate button, the reference cell is underlined, meaning I can see the IF/THEN reference cell formula (ie A1=1), the actual values (ie 32=1), and the boolean value (ie false).  When I run the Evaluate Formula Audit on the referenced randbetween cell (which displays 1), I get the following audit values from the pattern above: the named cell is underlined, giving the reference values (ie namedCell), the absolute reference (ie $A$1), and the final value of the function (ie a random value between 1 and 31).  So under this error method, I appear to be getting values as high as 32, which when namedCell > 32.

Interestingly, when I set namedCell to values less than 31, everything works perfectly.

I have tried to restart excel, but not yet the computer.  No luck.

I also tried manually inputing 45 (and 100000) for the upper bounds of the randbetween functions, still didn't see values higher than 31.

Weird, I know.

Thank you all in advance for this

Answer 1

I didn't get any replies, but I did figure it out.

I had formatted the numbers as dates.  23 was therefore 1/23/1900.  32 was 2/1/1900.

I figured it out when I looked at the static version of the page I had updated from. This is a random scheduler - final output needs to be static, but updatable and random on demand.

Thank you again for looking.

Answer 2

Hello,

We're glad to know that your issue is resolved and thank you for sharing the solution as this would help other users facing the same issue.

Thank you.

Answer 3

Update:

First, restarting the computer didn't do anything.

Second, I was able to manipulate the result a little more, although it still isn't working.

On the downstream dependent function, I changed my IF/THEN from =1 to <20.  Still, no displayed values were >31.  However, I might have gotten a random number of say 19, but the downstream dependent function yeilded no result as if 19>20.  I ran the Evaluate Formula Audit.  The reference cell is underlined, meaning I can see the IF/THEN reference cell formula (ie A1<20), the actual values (ie 40<20), and the boolean value (ie false).  Interesting for this example, 31 (the max displayed value) + 19 (the actual displayed value = 40 (the value the cell seems to think it is using).

Evaluate Formula Audits on the randbetween cell still results in the apparent value being the actual value.

Thank you again.