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?
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Could you provide a small example with a couple of sets of values alongside the result you'd expect?
 

smartliving

New Member
Joined
Jun 23, 2015
Messages
10

ADVERTISEMENT

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?
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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
 

smartliving

New Member
Joined
Jun 23, 2015
Messages
10

ADVERTISEMENT

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,604
Messages
5,597,128
Members
414,126
Latest member
jellevansoelen

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
Top