The formula in the image below in cell D25 is=IF(B25,C25, 0), and the formula is copied to the other cells in that column. This means cells in the D column should take on the value in the C column (same row) if the B column cell for that row evaluates to TRUE (e.g. =1). Yet, this one's evaluating to 1 instead of 0.5 as it should. Why?

Automatic formula calculation is on and I did a Calculate Now before taking this screenshot.

  • I removed "Excel" because it's tagged Excel - the application name is not needed. – Raystafarian Apr 13 '16 at 18:18
  • @Raystafarian Having the application name in the question still helps with SEO and helping searchers (on the site or broader Web) identify if the question is a fit for what they're looking for. – WBT Apr 13 '16 at 18:21
  • 1
    From the horse's mouth – Raystafarian Apr 13 '16 at 18:24

Excel is automatically rounding to the width of the column.

To display "0.5" the column width must be greater than 3.

Pressing Increase Decimal won't do a thing unless the column is wide enough, so the first step is to increase column width. Unless you've previously formatted the cell to round to integer values, that alone should solve it.

  • Documentation shows that if it's a width issue it will read #### and you will need to adjust the column width. Why would it act differently? – Raystafarian Apr 13 '16 at 18:14
  • @Raystafarian Apparently it only reads ### if the number is too wide after having already rounded to the nearest integer (i.e. if the number is too high for the column to display all digits.) – WBT Apr 13 '16 at 18:19
  • Do you have the documentation for this behavior? Please note that I know what you're saying is true. – Raystafarian Apr 13 '16 at 18:20
  • @Raystafarian No. I post this Q&A to try to create that documentation. The behavior does not match at least my expectations (which seem more in line with yours) and the unexpected behavior was unnecessarily frustrating for longer than it should have been. Hopefully this will help make things easier for the next person to come along and experience the issue. – WBT Apr 13 '16 at 18:25
  • It might be prudent to head over to the MS support forums and get someone to point to the documentation or at least get an MVP to explain. It would make the answer better - it's not bad or incorrect as is. I did +1 – Raystafarian Apr 13 '16 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.