Using IF function when "#value!" is present.

I have a problem. I think it can be solved with the IF funciton.

I need to choose between the greater value in two cells to appear in a
third cell.

Because of how the data is procured: every so often only one of the two
cells has a value in it and the other has "#VALUE!" and the IF Function
breaks down.

in that situation i just need the one value that exists

Is there a way to do this?


dan

wrote...
....
I need to choose between the greater value in two cells to appear in a
third cell.

Because of how the data is procured: every so often only one of the two
cells has a value in it and the other has "#VALUE!" and the IF Function
breaks down.

in that situation i just need the one value that exists

Is there a way to do this?

The most robust way to do this is something like

=CHOOSE(1+COUNT(X)+2*COUNT(Y),"no max",X,Y,MAX(X,Y))

Note that this also handles the case in which neither X nor Y is
numeric. You could also do this with IF calls, like so.

=IF(COUNT(X),IF(COUNT(Y),MAX(X,Y),X),IF(COUNT(Y),Y ,"no max"))

On 5 Sep 2006 13:57:44 -0700, wrote:

I have a problem. I think it can be solved with the IF funciton.

I need to choose between the greater value in two cells to appear in a
third cell.

Because of how the data is procured: every so often only one of the two
cells has a value in it and the other has "#VALUE!" and the IF Function
breaks down.

in that situation i just need the one value that exists

Is there a way to do this?


dan

you could try:

=IF(ISERR(A1),A2,IF(ISERR(A2),A1,MAX(A1:A2)))


--ron

Here's another one:

Entered as an array using the key combination of CTRL,SHIFt,ENTER (not just
ENTER):

=MAX(IF(ISNUMBER(A1:B1),A1:B1))

Biff

wrote in message
oups.com...
I have a problem. I think it can be solved with the IF funciton.

I need to choose between the greater value in two cells to appear in a
third cell.

Because of how the data is procured: every so often only one of the two
cells has a value in it and the other has "#VALUE!" and the IF Function
breaks down.

in that situation i just need the one value that exists

Is there a way to do this?


dan