AND Statement to Return TRUE or FALSE

[jski21]

Good day all,

Attempting to have a formula look at a date AND if a number falls between a certain range. If the date is prior to today AND the number falls between a range the result is TRUE. If not, it is FALSE. Here's the formula that I'm fighting with:

=AND(E28<$A$4,(D32>=(E26*0.5),D32<E26)

E28 = 12/31/2015
$A$4 = Today()
D32 = 19.4%
E26 = 25%

Basically, if the date is prior to today AND the value falls between 12.5% and 25% the statement is true. If not, false. The result of this statement will eventually be used to highlight Cell E32 with an amber color via conditional formatting. I'm likely missing/misplacing a comma or parentheses somewhere but have tried numerous iterations in what I believe to be correct. I'm not.

Target-Vs-Actual.xlsx
	D	E
26		25%
27
28	70.4%	12/31/2015
29	32.3%	2/28/2017
30	60.6%	2/28/2018
31	32.4%	1/31/2019
32	19.4%	12/31/2019
Dashboard

Cell Formulas
Range	Formula
E26	E26	=Pivot!K6
D28:D32	D28	=INDEX(Pivot!I:I,MATCH(1,(Data!$E$9=Pivot!A:A)*(Dashboard!A28=Pivot!B:B),0))
E28:E32	E28	=INDEX(Pivot!$K$7:$O$39,MATCH(Data!$E$9&A28,Pivot!$A$7:$A$39&Pivot!$B$7:$B$39,0),MATCH($E$26,Pivot!$K$6:$O$6,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E32	Expression	=AND(E32<$A$4,D32>=$E$26)	text	NO
E28	Expression	=AND(E28<$A$4,D28>=$E$26)	text	NO

Manny thanks in advance everyone.


jski

 

[Joe4]

You have an extra left parenthesis in there.
Try:

=AND(E28<$A$4,D32>=(E26*0.5),D32<E26)

or a little more simply:

=AND(E28<$A$4,D32>=(E26/2),D32<E26)

 

[jski21]

That's it. It's amazing what a second set of experienced eyes can do Joe4. Thanks!

 

[Joe4]

You are welcome.

A little trick that you can do is to try each condition as its own formula, and see which one errors out, i.e.
=E28<$A$4
=(D32>=(E26*0.5)
=D32<E26

You will see that the second one errors out, and when you look at it in isolation like that, it is easier to spot the issue.