• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Assigning symbol to positive/negative numbers in a cell

razaas

Member
Hello all,

I want to show (down arrow) if value in a cell is negative number and show (up arrow) if value is positive number . I tried this formula [=IF(I2=TRUE(),”p”,”q”)] and used font type wingding3, but it works only for negative value not changing when value is positive number. Any help is highly appreciated.
 
Hello


I assume your value is in the cell I2.


change the formula to

Code:
=if( I2>=0, "p", "q")


With 2007+ : Select your cells, then click on then Conditionnal "Formating button" in "Style group" / "Icon Set".


You'll see there 3 arrows "up green", "even yellow", and "down red". Select it.

Then Click on "modify rules" and adapt the rule created for your range. Put even to 0.
 
cyrilz,

Thanks for quick response, but the value in I2 is the result from a formula [=C15-SUM(F15:F20)] so it can change to either negative or positive as the values in the dependant cells changes. Hope it makes sense to you and everyone else.
 
Razaas

Try
Code:
=IF(I2<0,"q",IF(I2>0,"p",0))


Then change font to Wingding3
 
Hi razaas,


Just a thought, if the figure was neither positive or negative i.e. zero then you could use


=IF(I1<0,"q",IF(I1>0,"p","t"&"u"))
 
And Don't forget you can use conditional formatting to change the colors of the Negative (Red) and Positive (Green/Black) Arrows
 
Yeh,


Like the examples here


http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/
 
The report generated from our co. software in excel have negative figures suffixed as "-" for ex.

How should i Bring the negative sign for this type of data as prefix when there is huge data and also in a columnar manner.
 
Back
Top