Conditional formatting a Ratio

[Executioner]

Hi everyone,
I'm using Excel 2016.
I have a spreadsheet that is calculating a ratio with this formula:
=ROUND((C3/D3),1)&":1"

In C3 I have the value of 1
In D3 I have the value of 0.07
When the formula is applied in cell F3, the value returned is 14.3:1

I have other values that I'd like to highlight in red if the ratio is below 4.0:1. I've tried simply entering in 4.0:1 but it highlights all the cells as red. I changed the format from general to numeric for F3, but there is no change. Any help is greatly appreciated.

 

[etaf]

the ratio would be shown as text and not a number
BUT
ROUND((C3/D3),1)
is a number , and all your adding is the :1

so select the column you have the ratio in
then use the formula
=ROUND(($C3/$D3),1) < 4.0

note if you select the column , say the ratio is in E
then you would need to use
=ROUND(($C1/$D1),1) < 4.0

or if you select the range E3:E2000 or what ever row you want to stop then
=ROUND(($C3/$D3),1) < 4.0
will be OK

also blank cells give you what result - DIV by zero error ? or 0/0 = 0
and if you dont want to flag blank cells

=ROUND(($C3/$D3),1) < 4.0
use an AND

-AND ( C3 <> "" , ROUND(($C3/$D3),1) < 4.0)
if cell is the cell to determine if row blank

 

[Executioner]

Thanks for the reply. If I keep your suggested formula - =ROUND(($C3/$D3),1) < 4.0, the value return is only a FALSE or TRUE and not a value. I'd like to keep the reported value if possible.

 

[etaf]

you wanted to highlight in red - so the formula was only to be used in a conditional format rule

https://www.dropbox.com/s/ziksbr14fdwe0tb/condfrmt_etaf.xlsx?dl=0

 

[Executioner]

I did not know your formula had to be used in a conditional format rule. That does work! Thanks a bunch for your help.

 

[etaf]

you are welcome , i assumed from the title you where using the conditional formatting part of excel - sorry , i should not make assumptions