Forum Discussion
Using Copy/Fill Excel
I create a pattern to fill down. The pattern starts with 3.50%, in the next cell 3.75%. I select both cells and then use the copy/fill tool to copy down to 7.00%. After the fill, when I click on 6.25%, the value is rounded to 6.24999999%. This is a problem as I am using a separate cell that may contain any one of the values listed, but of course, it doesn't work for the values that rounded down. I am a teacher and have been teaching this lesson for 6 years. This is a new behavior for Excel. I am using Microsoft Office 365 Professional (Office 2021) (our school has a subscription). Thank you for your help.
6 Replies
Excel has always behaved this way, but when exactly it occurs is hard to pin down.
In your example, enter 3.5% in F5, then enter the formula =ROUND(F5+0.25%,4) in F6, and fill down from F6.
(We round to 4 decimal places since 0.01% = 0.0001)
- Sounds like something is broken. Thank you for the work around.
No, it is not broken - it is due to the way computers in general, and Excel in particular, handle fractions. We humans use decimal (10-based) numbers, while computers binary (2-based) numbers. Most decimal fractions cannot be represented exactly as binary numbers, so when converting from decimal to binary and then back from binary to decimal, tiny rounding errors occur.
That's Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps | Microsoft Learn which was in previous versions of Excel as well (and not only in Excel).
- Thanks. As I mentioned, this is a new behavior in Excel and why it starts at 6.25% doesn't make sense; I am dragging down (copy/fill) from a selection (3.5 & 3.75 are selected) so it should just continue to follow the pattern of increments at .25. What I am also noticing is that if I begin with a different value (10.5%, 10.75%) and copy down, it doesn't happen. Very odd. Thanks, again.
