Question About Conditional Formatting

[smartliving]

So I still haven't figured out an answer and I didn't want to bump my old post.

I have a set of data which is the percent change in closing prices of stocks for a bunch of stocks. I want to highlight a cell if the 10 proceeding dates add up to a total of +/- 3% or greater.


For example, if a stock's price had increased by .4 percent each day for 10 days, I want the cell to be highlighted because the total would be 4%. If a stock's price had decreased by .3 percent for 10 days, I'd also want to highlight the cell.

How in the world do I do this?

 

[smartliving]

Could you describe how you have implemented the solution?

Matty

I went to conditional formatting and clicked new rule and chose the option that allows you to input a formula. I copy and pasted the formula you gave.

B2 is highlighted even though it contains data for the oldest date, that is the issue. It adds upwards not downwards.

 

[Matty]

If I follow, then you could use the following formula within Conditional Formatting:

=ABS(SUM(B2:B11))>0.03

This assumes your data is in A2:B? and that the Conditional Formatting would therefore start from row 12 which is the first point where they'd be 10 days of history.


Make sense?

Matty

Hi,

Sounds like you didn't implement as mentioned above. You need to select from cell B12 down to the last row where you want the CF applying for this to work.

To reiterate:

Select cell B12:B112 (112 being an arbitrary last row), go to Conditional Formatting > New Rule > Use a formula... > and apply the following:

=ABS(SUM(B2:B11))>0.03

Format as desired.

As we are not anchoring the cell references in the formula, for every row down the formula will be summing the previous 10 cell values.

Matty