IF function with conditional formatting

dmf

New member
Joined
Sep 19, 2014
Messages
2
Reaction score
0
Points
0
Where do I begin..sorry first post to site. I am trying to use a sum to base all other criteria off of. I have column A (increment), B (hyphen), C (IF function and conditional formatting) on page 1 and the sum on page 2. The end goal is a very basic goal chart in which will fill column C with a color if the criteria is met in regards to the sum on page 2.

the base value is: =IF(AND(Sheet2!C30>249,Sheet2!C30<500),"True","") the conditional formatting will highlight the cell red if true.

the second value is:
=IF(AND(Sheet2!C30>499,Sheet2!C30<750,),"True","")

the problem comes in when the sum increases say to 600 the initial value turns false and then only one value is highlighted instead of them stacking and the base remaining highlighted as well. Boy I sure hope someone can understand what I am trying to accomplish. Thank you.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Where do I begin..sorry first post to site. I am trying to use a sum to base all other criteria off of. I have column A (increment), B (hyphen), C (IF function and conditional formatting) on page 1 and the sum on page 2. The end goal is a very basic goal chart in which will fill column C with a color if the criteria is met in regards to the sum on page 2.

the base value is: =IF(AND(Sheet2!C30>249,Sheet2!C30<500),"True","") the conditional formatting will highlight the cell red if true.

the second value is:
=IF(AND(Sheet2!C30>499,Sheet2!C30<750,),"True","")

the problem comes in when the sum increases say to 600 the initial value turns false and then only one value is highlighted instead of them stacking and the base remaining highlighted as well. Boy I sure hope someone can understand what I am trying to accomplish. Thank you.

Im a little confused. You haven't said which cell carries the formatting, but lets say its C30. Although you have two expressions one would be sufficient as below, because Cell C30 needs to be red if its value is anywhere between 249 and 750:
Try this conditional format formula for the cell you want to format:

=AND(Sheet2!C30>249, Sheet2!C30< 750)
 

dmf

New member
Joined
Sep 19, 2014
Messages
2
Reaction score
0
Points
0
Sorry I was not specific enough. The sheet2!c30 is a constant changing number that the argument is based on. If you would imagine a thermometer. I have increments of 250 from 250 to 40,000 (end goal). The formula I have work correctly but I need to add something else to them. So when c30= 750 the third row is true and it highlights but the two other rows (250 & 500) are now false, I need these to remain true as c30 increases.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Sorry I was not specific enough. The sheet2!c30 is a constant changing number that the argument is based on. If you would imagine a thermometer. I have increments of 250 from 250 to 40,000 (end goal). The formula I have work correctly but I need to add something else to them. So when c30= 750 the third row is true and it highlights but the two other rows (250 & 500) are now false, I need these to remain true as c30 increases.

OK Im assuming that you are conditionally formatting two different cells, in which case you need two separate expressions. In this case, because your formulae expressions are mutually exclusive, only one can return TRUE so the other is not going to show red.
I think that you must post an example spreadsheet, showing example values, and how these should affect the colouring of your formatted cells.
 
Top