[polanskygreg1985]

Basically im trying to change the fill color in cells based on their StDev. 1StDev=Yellow, 2StDev=Orange, 3StDev=Red, BUT, each column has a different StDev, so im forced to do this manually, but theres gotta be an easier way. I have close to 200 columns alltogether...

I tried recording a macro but can't seem to figure out how to get the conditional formatting formula code to move over to the next column...anyway, i don't want to make it anymore confusing so i'll stop talking, any help?

[Zack Barresse]

Hi there, welcome to the board!

Can you give examples, based on your data, what you're looking to format? What range? Are you talking about rounding up the standard deviation to a whole number? And what range should be looked at for each stdev? Please provide a little more information. The more you give us (real examples with your data works best) the better/faster you'll allow us to help you.

[Aj_old]

I suppose that you mean if the difference between cell value and the mean of the column is less than 1 StDev of that column than you wanna it yellow, and so on, in this case take a look at the attached file. If no then please give more details about the results you wanna get.

[polanskygreg1985]

I realize now how uninformative my first post was.

the data is daily change in natural gas prices from different indexes throughout the U.S. Im trying to analyze any patterns in volatility between these changes. I need to find and highlight changes in price that are relatively large for each particular index. Alltogether I have close to 200 indexes, with their own StDev. The file I uploaded is just a portion of my whole file, due to size.

So..each column is a different index with a unique StDev. I need to use this unique StDev to highlight cells in that particular column, for a total of 200 columns. The cells i need highlighted are cells that are 1StDev away from the mean (yellow), 2 StDev away from the mean (Orange), and 3 StDev away from the mean (Red).

I think thats its...

[Aj_old]

Did you got the chance to take a look at the file I uploaded?

[Zack Barresse]

@aj: I was thinking the same thing.

@Greg: See if Aj's file works for you or is what you're thinking. If running data is going to be a problem for you, you might want to think about having the data set of formulas in another location as opposed to the bottom, i.e. if it grows. But as far as I can tell, that is what you're looking for. Please let us know.

[polanskygreg1985]

This looks to be what i need, but i can't replicate it in my worksheet. How do i do this?

[Zack Barresse]

What do you mean you can't replicate it? Have you added the formulas? Once you add them you can select your range and add the conditional formatting, referencing the formulas. And what about my last post, can you shed some light?

[polanskygreg1985]

Thank you guys, i got it, not sure why i couldn't get each column to use the correct StDev

Thank you

[polanskygreg1985]

+, did you know that StDev is relative to the Mean, good to keep in the back of your mind. Thanks again