The most pointless combination of functions

[Lewiy]

I tested these - as far as I can tell there are several odd ones out.
The TEXT formula is the only one that doesn't return an error if A1 contains text.

If A1 contains 0, TEXT returns 0 and LEFT returns blank, the others return errors.
If A1 contains a 2 digit number, or a decimal such as 1.1, thing start to get interesting. Also the results for A1=10 are different from A1=11.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<o> </o>
You are quite right, there are various different results you can get from each depending on what value is entered in A1, however, the solution is a little deeper than that. The correct answer is SUBSTITUTE and here’s why:
<o> </o>
No matter what value/data type is in A1, be it an integer, a decimal, a text string, a boolean value or an error value, SUBSTITUTE is the only one that never has a result which is different from all the other three, there is always at least one of the other formulas which returns the same value as SUBSTITUTE. All of the others have uniquely different results from certain A1 values.

 

[Gerald Higgins]

It produces a specific distribution, i.e. more 1s than 2s and more 2s than 3s

Barry - you're absolutely right, although it took me a while to work out why - I was too hung up on RANDBETWEEN generating truly random numbers.

Of course it clicked when I remembered that the various instances of RANDBETWEEN in the formula are all returning different values - that's an important point to bear in mind when using RAND functions - using them sequentially might lead to non-random results.

 

[kgkev]

I can't see why its not random?

Each step has the same probability of generating each number

set 1 has a 1 in 3 chance of producing a 1

set 2 is the completely independant of set 1 and has a 1 in 3 chance of producing a 2

then stage 3 is completely independant of the previous 2 stages and has a 1in 3 chance of producing a 3

After all these stages there is a 1-3 chance of any of the numbers being generated.

Can you explain why this is not the case?

perhaps this would be more random?


=IF(RANDBETWEEN(1,3)=1,RANDBETWEEN(1,3),IF(RANDBETWEEN(1,3)=2,RANDBETWEEN(1,3),IF(RANDBETWEEN(1,3)=3,RANDBETWEEN(1,3),randbetween(1,3))))

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[cornflakegirl]

Time for my tablet, I think

Barry - can I be a geek, and ask how you worked out the distribution of 1s, 2s and 3s, please. Because I just spent 10 minutes creating a nice little tree, and I'm sure there must be a better way. But A level stats was a long time ago... Only if you haven't taken your tablet yet, obviously

 

[cornflakegirl]

set 2 is the completely independant of set 1 and has a 1 in 3 chance of producing a 2]

set 2 is completely independent - but the 2 will only trigger if set 1 didn't produce a 1.

Take yourself 81 lines in a spreadsheet, and write out all the outcomes of the 4 RANDBETWEENs, and the answer each produces - then it will be obvious!

 

[Gerald Higgins]

Set 2 is not completely independent of set 1. It has a 1 in 3 chance of producing a 2, if it is reached in the first place. But it only has a 2 in 3 chance of being reached at all - it won't be reached at all if set 1 evaluates to 1.

The probabilities of each part of the formula are something like this
Return 1 at stage 1 - 33%
Return 2 at stage 2 - 22% (67% chance of reaching stage 2, multiplied by 33% chance of returning a 2 if stage 2 is reached)
Return 3 at stage 3 - 15% (44% chance of reaching stage 3, multiplied by 33% chance of returning a 3 if stage 3 is reached)
Return 1 at stage 4 - 10% (30% chance of reaching stage 4, divided by 3)
Return 2 at stage 4 - 10%
Return 3 at stage 4 - 10%.

Adding all these up gives you probabilities of
43% for 1
32% for 2
25% for 3

Note, IANAS, so I may not have got this quite right

 

[Gerald Higgins]

Testing this empirically, the values do seem to trend towards what I suggested

 

[cornflakegirl]

Set 2 is not completely independent of set 1. It has a 1 in 3 chance of producing a 2, if it is reached in the first place. But it only has a 2 in 3 chance of being reached at all - it won't be reached at all if set 1 evaluates to 1.

Gerald - I think this is just a terminology thing. I'm assuming that you evaluate all four functions, and then work out what the answer is. In which case set 2 is independent of set 1.

But IANASE, so I could just be confusing myself!

 

[cornflakegirl]

Oh, and your values agree with what Barry posted: 35/81 = 43%, 26/81 = 32%, 20/81 = 25%.

 

[Gerald Higgins]

OK perhaps it is a terminology thing.
Step 2 will evaluate to 1, 2 or 3 completely randomly, with each having a probability of 33%. In that sense, it is independent of Step 1
BUT, the result of step 2 will only affect the overall result of the formula, IF Step 1 has NOT evaluated to 1. In that sense, Step 2 is dependent on the results of Step 1.