Using Copy/Fill Excel

Copper Contributor

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.

Screenshot 2023-09-20 134440.png

6 Replies

@Sweetow 

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.

@Sweetow 

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.

@Sweetow 

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.

Ah! Then I guess I need to let the textbook publisher know not to use fractions when wanting students to use the fill/copy tool. I for sure will let my students know to double check; they haven't learned the ROUND function yet! Thank you.