ANDIF (i think)
 

anyone fancy trying to solve an excel problem for me ?

i have 5 cells in a row.

A B C D Z

if there is a value in A and a value in B, then B should appear in Z.

if there is a value in A, no value in B, then C should appear in Z.

likewise if not B or C, then D should appear in Z.

For a value to be displayed in Z, A must be greater than 0.


--
chrisabberton
------------------------------------------------------------------------
chrisabberton's Profile: http://www.excelforum.com/member.php...o&userid=16872
View this thread: http://www.excelforum.com/showthread...hreadid=320531

I have actually solved this myself with some help from alan in chinks
post. thanks.

=IF(AND(H60,I60),I6,IF(AND(H60,J60),J6,IF(AND( H60,K60),K6,0)))

but now have a new problem.....

because A B C & D get their values from lookups, if the look-up cannot
find a value it is returning #N/A in some of these cells.

this is not a problem but it means that #N/A is shown in Z (above
example) for B, when it should be the value of C or D.

Can i force excel not to accept #N/A ?


--
chrisabberton
------------------------------------------------------------------------
chrisabberton's Profile: http://www.excelforum.com/member.php...o&userid=16872
View this thread: http://www.excelforum.com/showthread...hreadid=320531

If you want the value returned from the lookups when it does not find a
match to be for example 0 use the following formula:
=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))

Ï "chrisabberton" Ýãñáøå óôï
ìÞíõìá ...

I have actually solved this myself with some help from alan in chinks
post. thanks.

=IF(AND(H60,I60),I6,IF(AND(H60,J60),J6,IF(AND( H60,K60),K6,0)))

but now have a new problem.....

because A B C & D get their values from lookups, if the look-up cannot
find a value it is returning #N/A in some of these cells.

this is not a problem but it means that #N/A is shown in Z (above
example) for B, when it should be the value of C or D.

Can i force excel not to accept #N/A ?


--
chrisabberton
------------------------------------------------------------------------
chrisabberton's Profile:
http://www.excelforum.com/member.php...o&userid=16872
View this thread: http://www.excelforum.com/showthread...hreadid=320531

thanks Geovas,

i'm still struggling though, i'm adding the if(isna to the three
lookups, correct ?

it won't allow the formula. one of the 3 look-ups is

=VLOOKUP(B6,Power_Del,5, FALSE)

any further help would be appreciated....

= = = = =11.49 = = = =

sorry - i have actually solved it now, i was adding the isna part twice
in the same formula and wasn't using enough ().

cheers geovas.


--
chrisabberton
------------------------------------------------------------------------
chrisabberton's Profile: http://www.excelforum.com/member.php...o&userid=16872
View this thread: http://www.excelforum.com/showthread...hreadid=320531

Does this do what you want?

=IF(A1<=0,"",IF(B1<"",B1,IF(C1<"",C1,D1)))


On Fri, 26 Nov 2004 03:16:35 -0600, chrisabberton
wrote:


anyone fancy trying to solve an excel problem for me ?

i have 5 cells in a row.

A B C D Z

if there is a value in A and a value in B, then B should appear in Z.

if there is a value in A, no value in B, then C should appear in Z.

likewise if not B or C, then D should appear in Z.

For a value to be displayed in Z, A must be greater than 0.