Display on the cell with a value

[koba]

Hi All

I need help with a formula, I have 3 cell columns B,C & D with vlookup’s in each. The way I have set it up so that only one of the look ups will work. How do then make a formula that will be in column E and look columns B,C&D and display the only number that has worked

Eg.
A B C D
AT111 #N/A 0.71 #N/A


Please help

Thanks
Andrew

[pinmaster]

Maybe,

assuming only 1 number in the 3 cells.
=MAX(B1:D1)

HTH
JG

[koba]

Thanks

But it did not work because the problem is that the other two cells have errors in them so functions like max and large need to have whole numbers, i need a formula that would be able to read errors and then display to only cell without an error

Andrew

[Biff]

Hi!

One way:

=SUMIF(B1:D1,"<>#N/A")

Biff

"koba" <[email protected]> wrote in message
news:[email protected]...
>
> Hi All
>
> I need help with a formula, I have 3 cell columns B,C & D with
> vlookup's in each. The way I have set it up so that only one of the
> look ups will work. How do then make a formula that will be in column E
> and look columns B,C&D and display the only number that has worked
>
> Eg.
> A B C D
> AT111 #N/A 0.71 #N/A
>
>
> Please help
>
> Thanks
> Andrew
>
>
> --
> koba
> ------------------------------------------------------------------------
> koba's Profile:
> http://www.excelforum.com/member.php...o&userid=28639
> View this thread: http://www.excelforum.com/showthread...hreadid=501901
>

[Biff]

If the #N/A's are the result of Vlookups then MAX(............) will reutrn
#N/A.

Biff

"pinmaster" <[email protected]> wrote
in message news:[email protected]...
>
> Maybe,
>
> assuming only 1 number in the 3 cells.
> =MAX(B1:D1)
>
> HTH
> JG
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile:
> http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=501901
>

[koba]

Great thanks for the help!!

Almost there, while this formula displays the number i need which is good, it displays 0 when all three cells are #N/A, and in this situation need it to display #N/A or anything but a number!!!

Andrew

[Biff]

Hi!

Try this:

=IF(COUNT(B1:D1),SUMIF(B1:D1,"<>#N/A"),#N/A)

Biff

"koba" <[email protected]> wrote in message
news:[email protected]...
>
> Great thanks for the help!!
>
> Almost there, while this formula displays the number i need which is
> good, it displays 0 when all three cells are #N/A, and in this
> situation need it to display #N/A or anything but a number!!!
>
> Andrew
>
>
> --
> koba
> ------------------------------------------------------------------------
> koba's Profile:
> http://www.excelforum.com/member.php...o&userid=28639
> View this thread: http://www.excelforum.com/showthread...hreadid=501901
>