Solved

Some Positive cell values display in RED font

Posted on 2014-01-03
9
384 Views
Last Modified: 2014-01-07
I want to display the values in a financial report (created in Excel 2010) so that the values less than 0 will display with parentheses in Red font. When I apply the following format I get inconsistent results:  #,##0);[Red](#,##0) .

Strangely, in certain Excel reports, some positive values are also displaying in Red. Again, only SOME of the values are displaying incorrectly.  Does anyone know what causes this? These are formal, regular financial reports that must be correct.

See attached slice of a report.

Thanks for your help.
RedFont-Sometimes-Wrong.xlsx
0
Comment
Question by:thutchinson
  • 3
  • 3
  • 3
9 Comments
 
LVL 18

Expert Comment

by:Steven Harris
Comment Utility
The two examples you show are formatted as red text, and are not changed by the conditional formatting.

You can also see that the text color is slightly off from Text Formatting and Conditional Formatting colors:

Color Variations
0
 
LVL 15

Expert Comment

by:ChloesDad
Comment Utility
Following on from the first post by ThinkSpaceSolutions, the conditional format is only changing values that are <0. If you want to force >=0 values to black then you should add that as an extra condition in the rule that you have already set up.
0
 

Author Comment

by:thutchinson
Comment Utility
I was fooling around with conditional formatting to see if I could correct it.  I guess I chose the wrong red tone.

So, some of the values are actually text and don't easily convert to numbers when formatting is applied?  This file is an output from a cloud-based app.
0
 
LVL 15

Expert Comment

by:ChloesDad
Comment Utility
What are the values that you are concerned about?

Are they in your sample file already posted?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:Steven Harris
Comment Utility
So, some of the values are actually text and don't easily convert to numbers when formatting is applied?

Conditional formatting is "logical" at this stage and numeric versus text is kind of irrelevant.  If it runs across clear text, it would then be <0 and have formatting applied according to your rules.  Does that make sense?

I guess the main point would be start fresh with the data (all black text) and re-apply the conditional formatting. If there is an error, provide an example with the error displayed.
0
 

Author Comment

by:thutchinson
Comment Utility
CloesDad,
The values that I'm "concerned about"  are clearly indicated in far right column and Col1 is highlighted in yellow.

ThinkSpaceSolutions,
I will try your suggestion and try taking all values to black first and then apply conditional formatting.
0
 
LVL 15

Expert Comment

by:ChloesDad
Comment Utility
And by adding the extra conditional format they change to black.

I was referring to the text rather than numbers that you suggested will be imported into the spreadsheet
0
 
LVL 18

Accepted Solution

by:
Steven Harris earned 500 total points
Comment Utility
See if this looks any better.

I used Ctrl+a to select entire worksheet and then formatted all text to black.

Conditional Formatting was not changed and all values greater than or equal to 0 (>=0) are black, while all values less than 0 (<0) are red.
RedFont-Sometimes-Wrong--1-.xlsx
0
 

Author Closing Comment

by:thutchinson
Comment Utility
That's the one!  Thanks  Thinkspace!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now