REPT function not working correctly for values 1 and 2

[Windows Server]

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 ? !

 

 

View the full article