Comparing percentage values against constants, less than, greater than, and ranges

[bevtyler]

I have two small tables where I need to compare percentages in columns E, F, G to the values/ranges in A, B, C.
Is there an easier way to do this than the long formula I have started to build?
(file attached)



The yellow formula in cell [I2] is below, which has the following checks:
- IF: compare exact amounts
- IF: compare amount within +-2
- IF: the cell contains a "<" less than sign,
- then IF compare it to "only the numbers within the cell of the first table"; [ VALUE ((TEXTJOIN...)))) ] will return 18 for cell [A3]
- else IF: the cell contains a ">" greater than sign, then compare it to "only the numbers within the cell of the first table" ...
- Otherwise, "not greater than"

=IF((E2*100) = (A2*100),"green",
IF(AND((E2*100) >= (A2*100)-2, (E2*100) <= (A2*100)+2),"yellow",
IF(ISNUMBER(SEARCH("<",A2)),
IF((E2*100) < VALUE((TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")))),
"green",
"red"),
IF(ISNUMBER(SEARCH(">",A2)),
IF((E2*100) > VALUE((TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")))),
"green",
"red"),
"not greater than")
)))


I still need to add:
- Checking for a range (if there is a dash "-"), and determining how to compare the numbers

When I copy the yellow formula to the blue cells, then the first two IF statements break where the first table has a TEXT CHARACTER in it.
- I am multiplying the percentage by 100 to standardize the number set, since the second IF statement needs to add or subtract 2.
- As well, the [ VALUE ((TEXTJOIN...)))) ] returns a whole number rather than a percentage

Thanks for any help you can give!

 

[shamimarita]

Hello bevtyler,

Yes, you can simplify this quite a bit instead of continuing with multiple nested IF statements. If the criteria are in A2 and the percentages to test are in E2, you can use the following formula in I2 and copy it across:

=LET(
x,E2*100,
c,A2,
t,TRIM(SUBSTITUTE(c&"","%","")),
s,SUBSTITUTE(t,"–","-"),
n,IF(ISNUMBER(c),c*100,IFERROR(--SUBSTITUTE(SUBSTITUTE(s,"<",""),">",""),0)),
lo,IFERROR(--TEXTBEFORE(s,"-"),0),
hi,IFERROR(--TEXTAFTER(s,"-"),0),
IFS(
LEFT(s,1)="<",IF(x<n,"green","red"),
LEFT(s,1)=">",IF(x>n,"green","red"),
ISNUMBER(SEARCH("-",s)),IF(AND(x>=lo,x<=hi),"green","red"),
ABS(x-n)<1E-10,"green",
ABS(x-n)<=2,"yellow",
TRUE,"red"
))

This formula handles the different cases separately:

• 20% → Green if the value matches exactly.
• A value within ±2 percentage points of the target → Yellow.
• <18% → Green when the compared value is below 18%.
• >22% → Green when the compared value is above 22%.
• 18%-22% (or 18-22%) → Green when the value falls within that range.
• Otherwise → Red.

The advantage of LET is that the criterion only needs to be processed once, making the formula much easier to maintain.

Also, you don't need to convert the cells containing <, >, or ranges into normal percentages. The formula recognizes those as text criteria while still handling normal percentage cells correctly.

When you copy the formula from I2 across, the references will automatically change from A2/E2 to B2/F2, C2/G2, etc.