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

Status
Not open for further replies.
Dear All,

I am attaching an excel file and i need a conditional formula were the colors should come according to the colors shown in the excel sheet. I have been using this formula =AND(B5<$B$4,B5<>"") but found that its not given the correct colors in some of the values.

Can somebody correct the formula

Regards,

Sonjoe
 

Attachments

  • Working File.xlsx
    8.1 KB · Views: 4
HI @SONJOE JOSEPH ,

What's Criteria of Conditional Formatting? Please provide Output Result

Regard
Rahul Shewale

Hi how are you. In the attached file you will see Equity Curve heading. An investor invested with an amount of Rs.12000. In the 2nd transction his investment amount has grown to Rs.12,100 up by 100 which means green. In the 3rd his investment has become Rs.19500 which is down by 7400 points so its "red". In 4th transaction his investment amount is Rs.19800 which is up by 300 points which is green. So likewise this will continue. Hope u got the logic.

Working file attached ok
 

Attachments

  • Working File.xlsx
    8.3 KB · Views: 4
This does NOT match what you have given as your criteria above, so you need to explain your annotations:


12000​
12100​
GREENCORRECT
19500​
REDWRONG
19800​
GREENCORRECT
20500​
GREENWRONG
20200​
REDWRONG
20800​
GREENWRONG
 
This does NOT match what you have given as your criteria above, so you need to explain your annotations:


12000​
12100​
GREENCORRECT
19500​
REDWRONG
19800​
GREENCORRECT
20500​
GREENWRONG
20200​
REDWRONG
20800​
GREENWRONG
I have already explained it very clearly about the Equity Curve in detail. Please do read above
 
How very insulting of you to assume that I have not read your explanation! Not a great way to make friends and influence people ... :(

Your explanation goes up to 19800 - it implies that anything that is up should be green and anything that is down should be red. It does NOT explain the results you say you want above.

It is YOU, sir, who needs to explain in much more detail what you want.
 
How very insulting of you to assume that I have not read your explanation! Not a great way to make friends and influence people ... :(

Your explanation goes up to 19800 - it implies that anything that is up should be green and anything that is down should be red. It does NOT explain the results you say you want above.

It is YOU, sir, who needs to explain in much more detail what you want.

The amount 19800 is greater than the previous value 19500 so its green. Then going on from there 20500 is greater than 19800 so again green. Now the next value 20200 is less than 20500 so its red. So this way its goes on. Hope u got the logic.
 
Why is the solution offered in post #12 wrong?

You said this:

The amount 19800 is greater than the previous value 19500 so its green. Then going on from there 20500 is greater than 19800 so again green. Now the next value 20200 is less than 20500 so its red. So this way its goes on. Hope u got the logic.

Solution offered:

Sl.NoEquity Cuve
1​
12000​
Bigger - GREEN​
12100​
Bigger - GREEN​
19500​
Bigger - GREEN​
19800​
Bigger - GREEN​
20500​
Smaller - RED​
20200​
Bigger - GREEN​
20800​

You also said this:

In the 2nd transction his investment amount has grown to Rs.12,100 up by 100 which means green. In the 3rd his investment has become Rs.19500 which is down by 7400 points so its "red".

Why do you consider the third investment of 19500 to be lower than the previous investment of 12100? Nobody in this thread is understanding your logic here, so you need to explain it- as I said before, it is YOU who is failing the test here, not everybody else. Your explanations so far have not been at all clear, in fact the only thing that is clear to all of us (except you) is that your explanations are inadequate.
 
Last edited:
It's wearing thin: the more the OP implies that we are all idiots, the less I am inclined to offer help. At the moment, it's a bit like getting blood out of a stone trying to make him realise that there are discrepancies in his posts.
 
It is indeed extraordinary that the OP continues to insist that 19500 is 7400 LOWER than 12100, but that is what he holds to be true. Quite how we can overcome this mathematically, I know not. :rolleyes:
 
SONJOE JOSEPH
If You could write something helpful
eg what would be different then
You could get more correct replies.

It's You, who would need Your solution.

You should refresh some basic sentences from here:
eg:
  • When drafting a question, try and lay out the question in a clear and concise way.
  • Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
  • Don't leave out information more info is better
 

Attachments

  • Working File (2).xlsx
    10.9 KB · Views: 3
SONJOE JOSEPH
If You could write something helpful
eg what would be different then
You could get more correct replies.

It's You, who would need Your solution.

You should refresh some basic sentences from here:
eg:
  • When drafting a question, try and lay out the question in a clear and concise way.
  • Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
  • Don't leave out information more info is better


In the attached file IF B5 > B4 then green or else red, similarly if B6 > B5 then green or else red. Again if B7 > B6 then green or else red. So this continues to all the transactions. Hope u understood.
 

Attachments

  • Working File.xlsx
    8.2 KB · Views: 5
The attachment does not show us what you want. What you are describing is what you have been given in posts #5 and #12.

Rule for green: =OR(ISTEXT($B3),$B4>$B3)

Rule for red: =$B4<$B3

Put the rule for green at the top.
 

Attachments

  • PQ Working File-1 AliGW.xlsx
    9.7 KB · Views: 1
Last edited:
Status
Not open for further replies.
Back
Top