Forum Discussion
EternalVoid
Feb 05, 2025Copper Contributor
REPT function not working correctly for values 1 and 2
Hi all,
Either I found a bug in a way REPT function work, or I really do not understand something.
I'll explain step by step how to reproduce the problem.
In A1 we put 0, A2 put 0.1, in A3 0.2 and until A11 you sho1ld have value 1.
Now change the type of field to percentage to receive 0% in A1, 10% in A2.... 100% in A11.
Now in B1 we put below formula:
=REPT("|",A1*10)
Now we adapt rest of B cells up till B11.
We should have empty B1, one pipe "|" in B2 and so on, 10 characters in B11. It works as desired.
Now what I want to do is to have the oposite value in column C. I mean here that in C1 I want to have REPT for 100%, C2 for 90% etc, in C11 0%.
So I put below formula in C1 and adapt C2-C11 to have the same:
=REPT("_",(1-A1)*10)
I should have 10 characters in C1, 9 in C2, and so on. In C10 there should be one character - because (1-0.9)*10 = 0.1*10 = 1
Correct?
So in C11 there should be no characters.
Well.... it does not work like that for me for last 3 cells. There is 1 character in C9 (should be 2 because (1-0.8)*10 = 2), 0 characters in C10 and zero in C11 (this is ok).
Why it does not work for values 1 and 2? Well it works for values, because if I modify C10 to this:
=REPT("_",1) then it works. But this does not work:
=REPT("_",(1-A10)*10) while A10 = 90%, so 0.9
Why? Can somebody explain it to me? I could understand that REPT does not accept result of other formula as a parameter. But it only does not accept when the result is 0.1 or 0.2 as it works for this for example:
=REPT("_",(1-A6)*10) while A6=50% so 0.5
W F T ? !
I'm 99% sure this is due to round off errors in Excel. Excel does have a limited resolution of bits to represent a number and although 0.8 and 0.9 are only 1 decimal place to you and me, a computer is based on base 2 and therefor I suspect the closest representation is ever so slightly less than 0.8 and 0.9 and that REPT is designed to use some form of INT() of the number. IF you add a ROUND or MROUND into the equation it will work fine.
Intrestingly #1 - You can also use INT() of the value as shown in the image below, where I use =REPT("1",INT((1-A2:A12)*10)) and it works 'correctly
Interstingly #2 - if you set up the initial column using =SEQUENCE(11, , 0, 0.1) it also behaves 'correctly'.
If you want to learn more about EXCEL rounding errors there are a number of excellent discussion given in this forum.
- m_tarlerBronze Contributor
I'm 99% sure this is due to round off errors in Excel. Excel does have a limited resolution of bits to represent a number and although 0.8 and 0.9 are only 1 decimal place to you and me, a computer is based on base 2 and therefor I suspect the closest representation is ever so slightly less than 0.8 and 0.9 and that REPT is designed to use some form of INT() of the number. IF you add a ROUND or MROUND into the equation it will work fine.
Intrestingly #1 - You can also use INT() of the value as shown in the image below, where I use =REPT("1",INT((1-A2:A12)*10)) and it works 'correctly
Interstingly #2 - if you set up the initial column using =SEQUENCE(11, , 0, 0.1) it also behaves 'correctly'.
If you want to learn more about EXCEL rounding errors there are a number of excellent discussion given in this forum.
- EternalVoidCopper Contributor
Well SEQUENCE won't work in my example, as these percent values will be dynamically changing, as I am using that in a report. However INT solves the issue entirely.
Thank you for a successful solution to my problem :)
- m_tarlerBronze Contributor
I might recommend using ROUND or MROUND as the solution as these function are intended to round off the number correctly. The INT and SEQUENCE were mentioned more as curiousities than intended solutions. The fact that INT works sort of, IMHO, indicates this issue could be on the bug side in that REPT function should implement the same 'close enough' logic that the INT function must be doing.