Jump to content


Conditional Formatting Giving Unusual Result

conditional formatting ROUND

  • Please log in to reply
2 replies to this topic

#1 davisec52



  • Members
  • PipPip
  • 11 posts

Posted 16 April 2014 - 06:39 PM

Hi. This question refers to the amortization calculator discussed in earlier questions I submitted. And I have attached a fresh copy of the spreadsheet.


The issue is this: I have added conditional formatting so that when the loan balance equals zero ($K24=0), that everything below  ($B$25:$L$2023) will be white.  One formula changes the text to white and the other formula changes the fill to white.  This works quite nicely for most circumstances; however, there are a few idiosyncratic circumstances where the formatting refuses to apply.


I have found that when I enter 15 or 27 into the Loan Payments field, that the formatting fails to work and will display 0s and the contents of the other columns all the way down to row 2023.  One other consequence of this is that the "Actual No. Pmts" field under "Results" in the upper right shows 2000.


And my question: Do you have any idea why the conditional formatting fails to apply when these few numbers (15, 27, 41,47 for example) are entered as number of yearly payments?





Attached Files

#2 Jonathan


    Forums Administrator

  • Administrators
  • 887 posts

Posted 16 April 2014 - 09:23 PM

Hi Evan,


This problem goes back to rounding.  Your conditional formats are looking for a zero value, but instead they are finding something like 0.0000000001.  Because this is still greater than zero, your conditional formats aren't applied.  There are two ways that you could solve this:


1. Set your conditional formats to look for a value less than 0.01, meaning that values of 0.009 or less will be coloured white.  You can edit your conditional formats using the Rules Manager.  For instructions on accessing and using the Rules Manager, see the video lesson: Essential Skills Lesson 4-16: Manage multiple conditional formats using the Rules Manager.


2. Round your Ending Balance values to 2 decimal places using the ROUND function.  This is my preferred option, as it eliminates any 'off by one' errors.

For more on rounding, see the video lesson: Essential Skills Lesson 4-3: Format numbers using built-in number formats.


I have attached a copy of your workbook with the numbers rounded to 2 decimal places, which eliminates the problem.


Attached File  loancalculator.xlsx   183.16KB   181 downloads



As always, please feel free to reply if you need any more assistance.

Attached Files

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

#3 davisec52



  • Members
  • PipPip
  • 11 posts

Posted 16 April 2014 - 10:23 PM



Thank you again!  So, not rounding comes with certain perils.  The lesson is finally beginning to sink in.



0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users