• 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.

Conditional Formula based on a few rules?

SONJOE JOSEPH

SONJOE JOSEPH

Member
Dear All Excel Experts,

I was just trying to give a conditional formula but it was not working for me perfectly fine. Here is the attached file and here are the set of rules below:-

1. In CE you will find value 150000. Here the rule is if the value is a negative amount then in the next cell ie "A4" it should show as "UP" or else if the value is a positive amount then it should show as "DOWN".

2. In PE you will find value -200000. Here the rule is if the value is a negative amount then in the next cell ie "B4" it should show as "DOWN" or else if the value is a positive amount then it should show as "UP".

Attaching the working file OK.

Regards,

Sonjoe Joseph
 

Attachments

  • Cond Format.xlsx
    8.1 KB · Views: 9
If you wish to use conditional formatting or number formatting you have two distinct strategies.

The first is to negate the CE value and base the formatting on
= -1*SIGN(CE)
= SIGN(PE)​

The second, is to accept the numbers as they are but have separate formats for the CE and PE values; e.g. number formats
"Down";"Up";"No change";@
"Up";"Down";"No change";@
respectively.
 

Attachments

  • Cond Format (PB).xlsx
    16 KB · Views: 3
This
Peter Bartholomew said:
If you wish to use conditional formatting or number formatting you have two distinct strategies.

The first is to negate the CE value and base the formatting on
= -1*SIGN(CE)
= SIGN(PE)​

The second, is to accept the numbers as they are but have separate formats for the CE and PE values; e.g. number formats
"Down";"Up";"No change";@
"Up";"Down";"No change";@
respectively.
Click to expand...

Dear Friend,

This is working fine. But i'm just wondering how did u create the symbol & down. Just teach me how to do it. When i did its coming as -1 & -1 figures. Just see my file and tell me how to do it ur way ok

Regards,

Sonjoe Joseph

(Attached file)
 

Attachments

  • Check.xlsx
    8.4 KB · Views: 2
The {-1,-1 } is what you should get and what is needed for the conditional formatting icons. The thing that changes the display to {Up, Down} is the number format applied to the cells. If you open the format cells dialogue box from the Numbers group or the Cells group on the Home Ribbon tab you will be able to define a custom number format. Then enter the format below:

"Up";"Down";"No change";@

this will give:
"Up" for cells containing positive numbers;
"Down" for cells containing negative numbers;
"No change" for cells containing zero
the text, as it is, for cells containing text.

If you reference the cells from a formula, it is important to remember that the actual content of the cell is {-1, 0; +1} and not the text as displayed.
 
Peter Bartholomew said:
The {-1,-1 } is what you should get and what is needed for the conditional formatting icons. The thing that changes the display to {Up, Down} is the number format applied to the cells. If you open the format cells dialogue box from the Numbers group or the Cells group on the Home Ribbon tab you will be able to define a custom number format. Then enter the format below:

"Up";"Down";"No change";@

this will give:
"Up" for cells containing positive numbers;
"Down" for cells containing negative numbers;
"No change" for cells containing zero
the text, as it is, for cells containing text.

If you reference the cells from a formula, it is important to remember that the actual content of the cell is {-1, 0; +1} and not the text as displayed.
Click to expand...

Thank You Peter and now its working perfectly fine.
 
Peter Bartholomew said:
If you wish to use conditional formatting or number formatting you have two distinct strategies.

The first is to negate the CE value and base the formatting on
= -1*SIGN(CE)
= SIGN(PE)​

The second, is to accept the numbers as they are but have separate formats for the CE and PE values; e.g. number formats
"Down";"Up";"No change";@
"Up";"Down";"No change";@
respectively.
Click to expand...

Is there a possibility to give a sound if either of the signals CE & PE are UP UP or DOWN DOWN.
 
That one I can't help with; I use headphones for meetings but otherwise my computer is silent. Testing the condition is easy but ringing the warning bell is a different matter. Perhaps it would be best to post afresh as a VBA question,
 
Peter Bartholomew said:
That one I can't help with; I use headphones for meetings but otherwise my computer is silent. Testing the condition is easy but ringing the warning bell is a different matter. Perhaps it would be best to post afresh as a VBA question,
Click to expand...

Ok. I don't want it as VBA since i have given lots of calculations in the particular excel file. Anyway leave it...Thanks for responding
 
You must log in or register to reply here.
Back
Top