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

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Using IF function when "#value!" is present. (https://www.excelbanter.com/excel-worksheet-functions/108651-using-if-function-when-value-present.html)

[email protected]

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

Harlan Grove

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

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

Ron Rosenfeld

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

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

Biff

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

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

All times are GMT +1. The time now is 09:26 AM.