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

1
votes

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'm only an Admin. I'd appreciate any support I'm given. Thank you.

ExcelDatasheet: ExcelDatasheet

2
excelconditional-formatting
not clear rules of larger and largest date, a bit of cinfused. - Anabas
Hi, Thanks for your help. "LARGE" found the "newest" "greatest" date when I used it independently. I thought that I could incorporate it into this search as it worked well on a spreadsheet. Using Conditional Formatting is difficult and my knowledge is very limited. I'm not a programmer, I'm an admin and know very little about this. I do appreciate your support. - Robin Foster

2 Answers

0
votes

Formula to find last used (nonblank) row number in H1:H1000 is

=LOOKUP(2,1/($H$1:$H$1000<>""),ROW($H$1:$H$1000))

It seems you want to highlight the last "Number" entered in column H.

Enter the following formula in conditional formatting and apply it to $H$1:$H$1000 (for more rows, change the number of rows in formula also)

=ROW()=LOOKUP(2,1/ISNUMBER($H$1:$H$1000),ROW($H$1:$H$1000))

enter image description here

If you want to highlight "any" last value (last nonblank cell) in column H then

=ROW()=LOOKUP(2,1/($H$1:$H$1000<>""),ROW($H$1:$H$1000))
0
votes

Here is the conditional formatting.

=AND(H4>0,ISNUMBER(H4),N(A4)=MAX(IF($H$4:$H$15=H4,$A$4:$A$15)))

Here we use =And(Criteria1,Criteria2,Criteria3)

Criteria1: we use H4>0 to filter the blank cell. you can also use H4<>0
Criteria2: we use IsNumber to filter the "-" string.
Criteria3: we use MAX(If()) to find the duplicate number with the latest date.

enter image description here