Forum Discussion
REPT function not working correctly for values 1 and 2
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.
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.
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 :)
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.
Understood. It also solves my issue. Thanks a lot for your contribution.
