Max returning 0...I want to return blank

[punter]

Hello all. Thank you in advance for your help.

I am having any issue with my Max formula. The formula is

.

It is returning me the highest value as it should. But if there is no value in the three cells it is returning 0. How do I get it to return blank. I have been trying various array formulas but nothing seems to work.

I'll look forward to your replies.

Thanks

Eddie

[Phuocam]

Try this ...

=IF(Max(H1339:J1339)=0,"",Max(H1339:J1339))

[Pete_UK]

Wow !! Looking at your Join date, you must have been one of the very earliest to join this Forum.

You could do it like this:

=IF(COUNTIF(H1339:J1339,"")=3,"",MAX(H1339:J1339))

Hope this helps.

Pete

[Pete_UK]

@Phuocam

Zero might be a valid number for MAX to return.

Pete

[punter]

Thank you so much. This worked perfectly.

[punter]

I have been around a while.

[jeffreybrown]

As Pete points out, if zero is a valid number then you could just mask it with a custom format...

Custom Format

• Right click cell (or Ctrl + 1)
• Format Cells
• Number
• Custom
• Type: 0;;;

[punter]

Thank you Jeffrey for your help.

[jeffreybrown]

You are very welcome. We are happy to help.

[Phuocam]

Or try this ...

=IF(COUNT(H1339:J1339),MAX(H1339:J1339),"")

[Pete_UK]

As I understand it, the OP might have 3 blank cells - in this case he wants to see a blank as the result. However, if one or more cells contains a number (which could be zero), then you would want to see the result, so masking the zero or setting the cell to "" if the result is zero does not fully accomplish that.

Pete

[Tony Valko]

Here's another one...

=IF(COUNT(H1339:J1339),Max(H1339:J1339),"")