Display on the cell with a value
 

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

Display on the cell with a value
 

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

Display on the cell with a value
 

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


--
koba
------------------------------------------------------------------------
koba's Profile: http://www.excelforum.com/member.php...o&userid=28639
View this thread: http://www.excelforum.com/showthread...hreadid=501901

Display on the cell with a value
 

Hi!

One way:

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

Biff

"koba" wrote in message
...

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

Display on the cell with a value
 

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

Biff

"pinmaster" wrote
in message ...

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

Display on the cell with a value
 

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

Display on the cell with a value
 

Hi!

Try this:

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

Biff

"koba" wrote in message
...

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