Hi all, have recently been presented with a new function by the name of CURRENT() and am wondering if anyone can provide an example/explanation regarding how it works.
Also any links to video tutorials would be awesome. I ask as I couldn’t find any info in the Calc guide or online, oddly enough.
Hello,
referring to the solution which has been presented to you:
=RANDBETWEEN(1;7)+(CURRENT()>=A2) (in question Error 502 produced inconsistently with Randbetween/Choose nested function)
If you enter that formula into a cell, the following happens:
=RANDBETWEEN(1;7) (and the result is stored in some internal variable/register)+ operation appears, indicating that something should be added to the current value (and here is the buzzword and why the function is named the way it is named)CURRENT() provides access to the stored value of =RANDBETWEEN(1;7) in step above and in fact (CURRENT()>=A2) evaluateseither TRUE (1), if the condition is fulfilled (i.e. condition =RANDBETWEEN(1;7)>=A2 is fulfilled) and essentially calculates =RANDBETWEEN(1;7) + 1,
or FALSE (0), if the condition is not fulfilled (i.e. condition =RANDBETWEEN(1;7)>=A2 is not fulfilled) and essentially calculates =RANDBETWEEN(1;7) + 0.
Very briefly: Function CURRENT() returns the current value of a cell evaluation as it is just before CURRENT() is being called (very simple case: 1+CURRENT() yields 2).
Ref.: LibreOffice Help - CURRENT (which indeed is a bit a twisted explanation, but the examples are quite clear)
Hope that explains, what’s going on.
Thank you, that was very thorough. Was good to see it in the context of the question also.
So CURRENT basically represents whatever value is currently occurring in the calculation in which it is used rather than the result of the calculation post facto, allowing the User to refer to the present result of a calculation midway through the formula. Ok.
So to put it another way the CURRENT section of the formula evaluates whether the RANDBETWEEN result is greater than or equal to A2. If it isn’t, the boolean result is 0, and this is added to the RANDBETWEEN number in order to not modify it, as no modification is needed in order to avoid two consecutive iterations of the same number; they are already not the same.
However if the RANDBETWEEN result IS greater than or equal to A2, this results in the boolean 1, which is added to the RANDBETWEEN result in order to modify it in order to avoid two iterations of the same number.
Therefore wouldn’t =RANDBETWEEN(1;7)+(CURRENT()=A2) suffice, as randbetween numbers that are already larger than the number in A2 don’t need modification in order to avoid two respective iterations of the same number in each cell, just as randbetween numbers that are smaller than A2 don’t require modifying?
in order to modify it in order to avoid two iterations of the same number.
This is complete misinterpretation. We add 1 not to avoid two iterations of the same number, but to provide a random number from the filtered out set of 7 numbers: (let’s take 3 in A2) 1,2,4,5,6,7,8.
My formula produces these results for different results of RANDBETWEEN:
1 -> 1 + 0 -> 1
2 -> 2 + 0 -> 2
3 -> 3 + 1 -> 4
4 -> 4 + 1 -> 5
5 -> 5 + 1 -> 6
6 -> 6 + 1 -> 7
7 -> 7 + 1 -> 8
All cases are covered exactly once, no omissions.
wouldn’t =RANDBETWEEN(1;7)+(CURRENT()=A2) suffice
Your modification would make the wrong resulting set:
1 -> 1 + 0 -> 1
2 -> 2 + 0 -> 2
3 -> 3 + 1 -> 4
4 -> 4 + 0 -> 4
5 -> 5 + 0 -> 5
6 -> 6 + 0 -> 6
7 -> 7 + 0 -> 7
You never get 8; you get 4 twice as often as any other result (so you have not a random result, and you are safe to bet on A1+1 to always have better chances - 2/7 vs 1/7 for any other possible number, except when A2 is 7 or 8).
You may suggest to change it to =RANDBETWEEN(1;8)+(CURRENT()=A2) - but then you will have two problems: it will give you 9 sometimes; and it will still give twice as many A2+1 results as any other possible result.
Hmm - is this all about this question, or isn’t it rather about question Error 502 produced inconsistently with Randbetween/Choose nested function?