New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Conditional Format Not Returning Desired Results

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Conditional Format Not Returning Desired Results

Postby JABJC01 » Thu Aug 18, 2016 5:13 pm

I’m having difficulty with a conditional format and I would appreciate someone reviewing the attached worksheet and letting me know what’s preventing me from getting the desired results.

I have a conditional format in F9 that compares the value between F9 and C46. If the values are NOT equal, then the background color and font in F9 changes (background = red | font = Yellow).

C46 is the sum of C1:C45.

F9 is the sum of F1:F8 which sums column C to recap values based on year using a SUMSIF statement.

What's confusing me, the value in both F9 and C46 are the same, but my conditional formatting is treating then as not being equal.

If I change “any” values in column C (C1:C45), my value in both F9 and C46 correctly reflects the new totals and my conditional formatting works; however, if I change the value back to the original amount, F9 and C36 correctly reflects the original totals (both being the same), but my conditional format shows they are not equal.

I’m sure it’s something obvious that I’m overlooking, but I reviewed my conditional format for errors, ensure there were no other rules that would take precedence, numbering format, rounding, etc.
  • 0

You do not have the required permissions to view the files attached to this post.
JABJC01
Rookie
 
Posts: 10
Joined: Sep 4, 2015
Reputation: 0
Excel Version: 2010

Re: Conditional Format Not Returning Desired Results

Postby pecoflyer » Fri Aug 19, 2016 1:56 am

If you format cell C46 to 11 decimals, you will see that the 11th is 1, while is C9 it is not so

This could be due to floating point arithmetic (§https://support.microsoft.com/en-us/kb/78113)
Wrap the sum in C46 with the ROUND function ( with 2 decimals) and it works

BTW to build your yearly table you can also use a Pivot Table
  • 5

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Conditional Format Not Returning Desired Results

Postby JABJC01 » Fri Aug 19, 2016 7:31 am

precoflyer,

Thank you for your help. I called myself looking at rounding impact, but I did not go out to 11-digits. Also, thanks for the suggestion on Pivot Table.

PS, not sure how to mark my post as solved.
  • 0

JABJC01
Rookie
 
Posts: 10
Joined: Sep 4, 2015
Reputation: 0
Excel Version: 2010

Re: Conditional Format Not Returning Desired Results

Postby pecoflyer » Fri Aug 19, 2016 9:02 am

You're welcome
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010


Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 49 guests

cron