Conditional Formating - work around?

kph

New Member
Joined
Jul 12, 2008
Messages
7
I need to do some conditional formatting. I am using the formula selection.
My formula contains more than 3 conditions.

What is the work around in Excel 2010 not using VBA method.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
There is no limit (other than length of formula) to the number of conditions you can have in conditional formatting.

The limit is the number of formats that can be applied.


Can you describe your conditions, and the formats to be applied to those conditions...


edit...
After reading further, you say you have XL2010...
The limit of 3 is only in XL2003 and earlier..
 

kph

New Member
Joined
Jul 12, 2008
Messages
7
After reading your response - do I understand correctly that the xl2010 is limited only to 3 conditions. Does the xl2011 allow for unlimited?

My formula has 4 conditions which put a word in the cell with a color shading.
Here is my formula -

=IF(AND(H9>0,I9>0),"Green",IF(AND(H9>0,I9<0,),"Blue",IF(AND(H9<0,I9>0),"Red",IF(AND(H9<0,I9<0),"Orange",""))))
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Nope, Version 2003 and earlier are limited to 3 conditional formats.
Versions 2007 and above can do significantly more than 3 conditional formats.
Not sure how many, but more than sufficient.

You enter 4 different conditional format formulas.

=AND(H9>0,I9>0) - format Green
=AND(H9>0,I9<0) - format Blue
=AND(H9<0,I9>0) - format Red
=AND(H9<0,I9<0) - format Orange


Hope that helps.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

What should happen when either H9 or I9 = 0?
 

kph

New Member
Joined
Jul 12, 2008
Messages
7
Shg - in regards to if it is zero - it will post no color or text. The objective is to have a visual (color) that can be quickly scanned.

This formula is for scouting stocks in relationship to the closing price and volume - measured 10 days apart. The green and blue are the ones will be most profitable, while the orange and red - stay away from.
 

kph

New Member
Joined
Jul 12, 2008
Messages
7
Here is the formula that is used to measure the present price and the 10 day old price -- =100*((D4/G4) -1) and a similar one for the present volume and the 10 day old volume.

Then you use the previous formula to measure the two which will tell you which is most valuable.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,289
Messages
5,600,751
Members
414,405
Latest member
Zaurb

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