Conditional formatting => removing -ve sign from text

[stubborn_excel]

Hi

I've created an IF function that returns a positive and negative value, when it's > eg 10,000 or <-10,000, the conditional format is supposed to display "TOO BIG".

That works fine, however when the cell value is negative, it insists on putting a "-" sign in front of the "TOO BIG", so it actually displays "-TOO BIG".

I can't figure out what to do get rid of it.

Thx

Stubborn_excel

[:) Sixthsense :)]

Is it conditional formatting is returning the text "TOO BIG"?

[dredwolf]

Whats the cell formatted as?
Whats the CF formula you are currently using?
A sample workbook (NOT a screenshot!) showing the type of data, expected outcome, and where the problem currently occurs would probably get you quicker results..no confidential/personal/sensitive or private info in the sample please !

[stubborn_excel]

Thx for helping out.

Cell is formatted as a number.

CF formula is....if not between eg +/- 9000, then "TOO BIG".

Works fine except for insisting on the negative sign in front of "TOO BIG" for values <-9000.

[:) Sixthsense :)]

What version of excel you are using I have never seen that the Conditional Formatting returning Text Character in Cells.

Please correct me if I am wrong...

[dredwolf]

well...first thing I suggest is format as General..Excel canhadle numbers,text, dates quite well under general..when you need SPECIFIC formatting, then go to the other formats,
Second...that does not give me a formula to see if there's an error in how its's written...that's why I asked for a sample workbook, then I can see if how the formula is written is creating the problem or not, and see WHY if it is...otherwise, I am just guessing, and probably guessing wrong...

[dredwolf]

@ Sixth
True...me neither..

[:) Sixthsense :)]

@ dredwolf,

If OP says Yes, then today we will learn one New Method. Awaiting for the reply...

[dredwolf]

@ Sixthsense....ummm, is it too late to hide?....lol

[stubborn_excel]

Here is a simplified workbook example of the problem I'm experiencing.

Thx again.

[stubborn_excel]

Version: Excel 2010

[:) Sixthsense :)]

Highlight both the cells and apply the following conditional formats:

1) Cell Value is greater than 0
2) Cell Value is less than 0

Then format each condition accordingly

[:) Sixthsense :)]

@ dredwolf,

No lesson from this OP today as usual normal question.

[stubborn_excel]

So the only difference is that instead of doing a "cell value is not between -9,000 & +9,000" i now do two separate rules?

I tried that and it hasn't worked.

[:) Sixthsense :)]

Oops sorry.. Ignore my Post #12. Suggested wrongly due to oversight. I will get back to you.

[dredwolf]

the problem is, the cell SIGN won't change, so its either a negative or positive "Too Big", and I have no clue how to change it through CF...sorry

[:) Sixthsense :)]

No need of conditional formatting. Just format the cell using the below expression in custom format that's enough.

The below Custom Format will Show Numbers when the value is less than -9000 with negative values and <9000 for positive values otherwise it will result Too Big Message

Please Login or Register  to view this content.

[dredwolf]

Okay...this as a formula for new rule...:
=OR(C5<9000,C5>9000)
This as the custom format :
"TOO BIG";"TOO BIG"

the string after the semi-colon applies to - values, so it works...hopefully...

[dredwolf]

@ Sixthsense, Well, I learned 3 new things on this one!

[stubborn_excel]

Thx Sixthsense. That works. Well played.

Thx also dredwolf.

Appreciate all your help.

Sorry - I think I found the pink * to indicate your assistance. Not sure how to mark it solved though.

[dredwolf]

To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

but check my solution as well...no cell format change, just the conditional, 1 rule.. as an option...

Edit

This also leaves your cells formatted as general, so no special handling required for accessing the info that is still underneath the CF...just a thought

[:) Sixthsense :)]

@ stubborn_excel,

Thanks for the rep and feedback

@ dredwolf,

Glad you learned 3 from this post