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

 

[Fluff]

If large is giving an error then H64 & I64 do not contain numbers. They are either text or blank

 

[RoryA]

If they both have 0, LARGE should return 0. If they are both empty, then you would get an error, but you could just use MAX instead.

 

[NigelPatrick]

If large is giving an error then H64 & I64 do not contain numbers. They are either text or blank

No they are both definitely formatted as numbers and contain a Zero

 

[Fluff]

The cell format is irrelevant, what do you get with

=isnumber(H64)

& the same for I64

 

[NigelPatrick]

If they both have 0, LARGE should return 0. If they are both empty, then you would get an error, but you could just use MAX instead.

Thanks using MAX worked. Obviously only would be a valid solution when I am looking for the highest 1 value in a range, but thanks you have me sorted for now.

 

[NigelPatrick]

The cell format is irrelevant, what do you get with

=isnumber(H64)

& the same for I64

FALSE

 

[RoryA]

There's your problem then - they are not numbers.

 

[Fluff]

In that case they are text & not numbers.

 

[RoryA]

Obviously only would be a valid solution when I am looking for the highest 1 value in a range

I tend to answer the question that is asked...