Help with a formula

[fishcakes]

I want to make a formula or perhaps conditional formatting. Where if the a Cell (D90 is not 100%) and if the Cell (F90) is today's date or past today's date, then the (F90 Cell will turn/fill with RED).

 

[Cubist]

Is there way to do this in conditional formatting opposed to having to do 100 rules?

You do not have to create a rule for each row.
Select all the rows you want to apply to -> Conditional Formatting -> Use formula to determine which cells to format -> Insert the formula -> Choose your format -> OK.

 

[saboh12617]

Hello,

I think we misunderstood. On my last screenshot, if you write =$F$1:$F$100 where i put the red arrow (you can also click the [↑] on the right to select the cells), you are going to apply the rule to all the cells, row by row. It's only one rule that you need.

 

[fishcakes]

Is there another conditioning rule that I can setup where if the a Cell (D90 is not 100%) and if the Cell (F90) is today's date or -3 days from today's date then the (F90 Cell will turn/fill with Yellow). For example, if Cell D90 is 75% and Cell F90 is 12/5/24, I want cell F90 to be yellow.

Quote Reply
Report

 

[saboh12617]

Yes well same principle, you can adapt the previous formula like so

=AND(D90<100%, F90<=TODAY() - 3)

However you should be careful of the order of the conditions in the list, because they are overlapping. I think you want the new rule on top of the old rule. Try to move them with the buttons to get your expected results. You can also use the button "Stop if true" to prioritize a condition over another one.

I think this page can help to understand all of this Excel conditional formatting formulas based on another cell.

Have a good day.

 

[fishcakes]

This Helps... One last thing, based upon the formula =AND(D90<100%, F90<TODAY()-3). I think I worded this incorrectly. I wanted the formula to capture any days between today-1 to -3. For instance if the day is dec 1/2/3, then the cell would turn yellow.

Yes well same principle, you can adapt the previous formula like so

=AND(D90<100%, F90<=TODAY() - 3)

However you should be careful of the order of the conditions in the list, because they are overlapping. I think you want the new rule on top of the old rule. Try to move them with the buttons to get your expected results. You can also use the button "Stop if true" to prioritize a condition over another one.

I think this page can help to understand all of this Excel conditional formatting formulas based on another cell.

Have a good day.

 

[lostmakingcharts]

Help in excel that has to with liquid - Tank when full is 420 inches each truck load taken out is 10inches or 10%. Adding product inches or %.

 

[saboh12617]

Hello,

Simply write each condition in the AND function. When using upper and lower bounds you have to separate them like this :

=AND(D90<100%, F90>=TODAY() – 3, F90<=TODAY()-1)

 

[fishcakes]

Did not work

 

[AlphaFrog]

Select all cells you want to conditional format (CF): e.g. F90 to F190
Put in the original CF formula =AND($D90<100%, $F90<=TODAY())
Excel will automatically adjust the row numbers in the formulas for all the selected cells

Alternatively:
Apply the conditional format to just F90
Then grab the cell handle (small black box in the lower right of the outlined cell) and drag down the column to copy the formatting
Again, Excel will automatically adjust the row numbers in the formula.