Forum Discussion
Conditional formatting
Hi Everyone, First timer here.
I am building some spreadsheets and I am having some issues with conditional formatting.
The sheet contains results from laboratory water testing and results are often reported as a number like <10. Due to testing accuracy reasons its reported as <10 rather than say 5.
With the conditional formatting I am highlighting results that are greater than a specific limit. The issue I run into is any result containing the "<" symbol will be highlighted regardless.
My question is, how do I have the conditional formatting either;
1. exclude any cell that contains the "<" symbol or
2. interpret the < as just the number (eg <10 as 10)
Keeping in mind I need to display it with the "<" symbol
In the photo below, all cells have the formatting to highlight in red any number greater than 10, all cells with the "<" symbol should not be highlighted.
Appreciate your assistance.
4 Replies
Use something like this in the conditional formula:
=VALUE(SUBSTITUTE(B3,"<",""))
Basically you're just telling xl to drop the '<' if it's there and treat the result as a number.
Assuming the first number is in the cell A2 and last number is in the cell C12, then select the range A2:C12 and make a New Rule for Conditional Formatting using the formula given below and set the format as per your choice.
=AND(ISNUMBER(A2),A2>10)Remember to replace A2 with the first cell of whatever is your data range .
This can be achieved by using 1 more validation set.
-> Format only cells that contain - Specific Text and beginning with - < (No double codes)
This is in addition to existing data validation on number greater than 10.
- if possible, pls share the file
