Handling =LARGE Datasets with Zero values

[NigelPatrick]

Hi all.
I have a function as follows =(SUM(J64:N64)+LARGE(H64:I64,1))

That returns a #NUM! error because H64 and I64 both contain a value of 0 (Zero). However Zero is a valid value so how do I change this formula to return the sum of J64:N64 plus Zero?

Thanks in advance

 

[Peter_SSs]

Then MAX is not really returning the correct value either. If they are text and both set to 1, MAX will still return 0
You could use either of these

=SUM(J64:N64)+MAX(--H64:I64)

=SUM(J64:N64)+LARGE(--H64:I64,1)

 

[NigelPatrick]

FALSE

The cell format is irrelevant, what do you get with

=isnumber(H64)

& the same for I64

And for info H64 contains this formula =IFERROR(VLOOKUP(A64,'MON 26-5-24'!A:N,14,FALSE),"0")

 

[NigelPatrick]

Then MAX is not really returning the correct value either. If they are text and both set to 1, MAX will still return 0
You could use either of these

=SUM(J64:N64)+MAX(--H64:I64)

=SUM(J64:N64)+LARGE(--H64:I64,1)

OK thanks Peter this worked.

 

[Alex Blakenburg]

Get rid of the quotation marks around your 0, it is forcing it to return a text string 0

=IFERROR(VLOOKUP(A64,'MON 26-5-24'!A:N,14,FALSE),0)

 

[RoryA]

Then MAX is not really returning the correct value either

If LARGE was working other than for 0, then other numbers had to be numbers. (as confirmed by the later post)

 

[Peter_SSs]

If LARGE was working other than for 0, then other numbers had to be numbers. (as confirmed by the later post)

I don't think it was stated anywhere that it was working for other numbers & the subsequent confirmation "using MAX worked" could still have been referring to when the cells contained 0 since that was the circumstance of the original question.
Never-the-less I should have said "MAX may not be returning the correct value"