1

I want to do a MAX value for the latest date in my data for the "Actual" column, but, say that one cell has no date, I want the MAX (in yellow) to be blank too. Any help would be appreciated!

Data from Table Image:

enter image description here

2 Answers 2

2

I suggest the following formula, assuming that the "Actual" column is C:

=IF(COUNTBLANK(C2:C8)=0,MAX(C2:C8),"")

Or more general:

=IF(COUNTBLANK(range)=0,MAX(range),"")
3
  • Thank you, this worked perfectly when there are some blank cells. But I am now having trouble getting a value for columns which do have all their dates filled in, when I use this formula I get "00/01/1900" as the MAX date whenever it should really be the 19/01/2022 for example
    – Se OKane
    Nov 1, 2021 at 10:13
  • Do all the cells have the Date format?
    – harrymc
    Nov 1, 2021 at 10:44
  • Yes the issue was the format Harry, it is sorted now - thanks for the help!
    – Se OKane
    Nov 1, 2021 at 10:52
-1

You may use any of these also:

=IF(SUMPRODUCT(--(C2:C8=""))>0,"",MAX(C2:C85))

or

=IF(COUNTIF(C2:C8,"")>0,"",MAX(C2:C8))

N.B. Adjust cell references as needed.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .