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

D

db_murray

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
 
H

Harlan Grove

[email protected] 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?
Click to expand...

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"))
 
R

Ron Rosenfeld

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
Click to expand...

you could try:

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


--ron
 
B

Biff

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
 
Top