Conditional Formatting Question - Excel (AND,ISNUMBER,LARGE)

Question

Hi, 

I can't write this correctly.  I'd like to highlight the cells in the H column that are a number (ISNUMBER) while matching them with the greatest date (LARGE) in the column A. 

I tried: 

=AND(ISNUMBER($H4),$A4=LARGE($A$4:$A$15,1))

H4 contains 6.93

A4 contains 1/1/2020 - correctly formatted as date 

Conditional formula: =$A4=LARGE($A$4:$A$15,1) works fine, standalone.  The value 12/1/2020 highlights.  

Conditional formula: =ISNUMBER($H4) highlights the correct values, standalone. 

I really need this formula to highlight H9 as it reflects the LARGE date 06/01 and the ISNUMBER cell H9 that has the value 6.01 and the largest date with a number in column H. 

I don't know where I'm going wrong.  My syntax is bad.  I'd appreciate any support I'm given. Thank you.

2020 - SICK LEAVE	~NAME~	HIRE DATE:
Month	Total Hours Worked	Sick Hours Accrued	(-)	Sick Hours Used	Date used	(=)	New Total
1/1/2020			(-)			(=)	6.93
2/1/2020	125.5	3.14	(-)	4	29-Jan	(=)	6.07
3/1/2020	122.5	3.06	(-)	0		(=)	9.13
4/1/2020	137	3.43	(-)	9	25-Mar	(=)	3.56
5/1/2020			(-)			(=)	-
6/1/2020	98	2.45	(-)	0		(=)	6.01
7/1/2020			(-)			(=)
8/1/2020			(-)			(=)
9/1/2020			(-)			(=)
10/1/2020			(-)			(=)
11/1/2020			(-)			(=)
12/1/2020			(-)			(=)

Answer 1

Hello,

Looks like you want to find the last numeric value in column H. You don't need additional criteria to check against Date column, as finding the last numeric value will do the job.

To find the value of the last non-empty cell in a row or column, you can use the LOOKUP() function. This formula is not an array formula and has performance advantages.

If you want to find the last numeric value in column H use this formula:

=LOOKUP(2,1/(ISNUMBER($H$4:$H$14)),$H$4:$H$14)

To highlight it, enter the following conditional Formatting:

=H4=LOOKUP(2,1/(ISNUMBER($H$4:$H$14)),$H$4:$H$14)

Let me know if you find this helpful!

Answer 2

Try a CFR on H4:H99 based on the following formula.

=AND(ISNUMBER($H4), $A4=AGGREGATE(14,7,  ($A$4:$A$99)/($H$4:$H$99<>""), 1))

Later versions of Excel could use MAXIFS to find the largest date where column H is not blank. Earlier versions could use INDEX in the method described in MINIF, MAXIF and MODEIF.

Answer 3

For large numbers that will never appear, I like to use 1e99 (a 1 followed by 99 zeroes. I've found it to be the largest number with the fewest keystrokes.