Conditional Formatting Formula

[STEVENS3010]

Hey guys,

I'm trying to add text to a cell based on another cell's value using conditional formatting. (i.e. when a certain numerical value is input in one cell, a text will display in another cell).

In this scenario I'm trying to make the text display if the numerical value is less than 5.

I've added the below formula but I'm not getting the text display in the cell where I've added the formatting. Any ideas???

=IF(F4<5,"Bottom 50%","")

 

[Joe4]

Thanks both - although unfortunately neither of the above appear to work?

Then you have not clearly clearly communicated it is to us exactly what you are trying to do here.
If you are having a hard time explaining it, then show us some sample data and your expected output.

 

[STEVENS3010]

Apologies for not being clear.

I have a sheet where I am trying to add conditional formatting to cell B9. The formatting applied is conditional of the value being less than 75% in the same cell (B9), and also the value entered in cell F2.

I am trying to format the cell when B9 is less then 75% and the input in cell F2 is either "red", "blue" or "green" rather than creating a new rule for each "red", "blue" or "green"

 

[Fluff]

Maybe

=AND($B9<=75%,OR($F2="Red",$F2="Blue",$F2="Green"))

but you haven't said how it's not working.

 

[Joe4]

Apologies for not being clear.

I have a sheet where I am trying to add conditional formatting to cell B9. The formatting applied is conditional of the value being less than 75% in the same cell (B9), and also the value entered in cell F2.

I am trying to format the cell when B9 is less then 75% and the input in cell F2 is either "red", "blue" or "green" rather than creating a new rule for each "red", "blue" or "green"

The formula that I gave you should do precisely that, provided cell B9 is actually a number and not text, and the value in F2 exactly matches what you put in your formula.

 

[STEVENS3010]

For some reason, the formatting is not being applied when I use the formulas you guys have kindly provided.

When I use the 'AND' function with only one value it works fine, but adding the 'OR' function doesn't appear to do anything - no formatting is applied.

=AND($B$9<75%,$F$2="Red")

- works fine

=AND($B9<=75%,OR($F2="Red",$F2="Blue",$F2="Green"))

- doesn't work

 

[STEVENS3010]

Ahhh, I've made it cell $B$9 and it now works! Thank you both for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

Ahhh, I've made it cell $B$9 and it now works! Thank you both for your help!

Do you understand what those "$" do?
They "lock down" the range reference that follows it.

So, if you wanted to format a multi-cell range, but wanted them all to look EXACTLY at B9 (and not B9, B10, B11, etc), then you need to lock that reference down like that.
Sometimes you may want it to lock down, other times you many want it to "float" (i.e. if you wanted each row to look at the column B reference in that particular row).

See here for more details: Excel Formulas: Relative and Absolute Cell References

 

[STEVENS3010]

Thanks - appreciate it.

 

[Liz001]

Conditional formatting cannot "add" a value to a cell, it can only affect the way it's displayed.

You would need to put that formula in the cell.

I was wondering if you can explain how to "put a formula in a cell", as you mentioned, above? (I understand auto sum, but unsure how to add a specific formula into a cell)

Background information: I am trying to change a cell with text so that Power Automate will be triggered. I have a spreadsheet of training courses and dates completed. When the dates are earlier (or less than) June 1st, 2022, they have "expired". When the dates are later than (or greater) than 6/30/2022 then they are "current". I prefer one column for each course, but I can add a due date column for each course if necessary.

The conditional formatting of changing the font/background color after a date change is not triggering power automate to email me.