ANDIF (i think)

[chrisabberton]

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]

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

=IF(AND(H6>0,I6>0),I6,IF(AND(H6>0,J6>0),J6,IF(AND(H6>0,K6>0),K6,0)))

but now have a new problem.....

because A B C & D get their values from lookups, if the look-up canno
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 (abov
example) for B, when it should be the value of C or D.

Can i force excel not to accept #N/A

 

[Geovas]

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]

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.

 

[Myrna Larson]

Does this do what you want?

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