Question About Conditional Formatting

smartliving

New Member
Joined
Jun 23, 2015
Messages
10
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?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Could you provide a small example with a couple of sets of values alongside the result you'd expect?
 
Upvote 0
Of course.

3/18/20150.5%
3/19/20151.7%
3/20/2015-0.6%
3/23/2015-0.8%
3/24/20151.0%
3/25/2015-1.7%
3/26/20150.3%
3/27/20150.3%
3/30/2015-4.5%
3/31/2015-2.4%

<colgroup><col><col></colgroup><tbody>
</tbody>

is the data I have for one stock. Cumulatively, that adds up to -6.1%. So I would want 3/31/2015 cell to be highlighted because the previous 10 days were cumulatively greater than +/- 3%.

4/1/2015 .8%

So again, I would want the cell for 4/1/2015 to be highlighted because the previous 10 days were cumulatively greater than +/- 3%. And so on, for every date where this holds true.

Does that make sense?
 
Upvote 0
If I follow, then you could use the following formula within Conditional Formatting:

Code:
=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
 
Upvote 0
That works, except my date is in descending order with oldest date on top. So it should be adding downwards, not upwards. How do I fix that?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top