Need a function to fill column from other columns

[Guest]

Hi all,
I am thinking that a long nested IF statement will accomplish my goal, but
I'm not sure how to lay it out. I have a blank column (M) that I want to fill
with a value from either column N, O, P, Q, or leave blank. Columns N, O, P,
and Q may be blank or contain a number (which may be zero). I want to go in
order, so that if the value in column N is not null and not zero, use that
value to fill column M; otherwise skip to column O and apply the same test,
and so on. If I get all the way to column Q and all the columns N-Q are blank
or zero, then I want to leave column M blank. I'll try and write it out:
If N2 is not null and N2>0, then M2=N2, else M2=O2
If O2 is not null and O2>0, then M2=O2, else M2=P2
If P2 is not null and P2>0, then M2=P2, else M2=Q2
If Q2 is not null and Q2>0, the M2=Q2, else M2=""

Any suggestions? My thanks in advance.
Gretta

 

[Mark]

you might try this formula in M1:=SUBTOTAL(4,N1:Q1). That will always
yield whatever the maximum value is in N1:Q1. I'm not sure if that is
the same as what you asked, but I am guessing that should do the same
thing. Look at the help on the Subtotal function for more options.

 

[Guest]

Try the below formula which assumes the values you want to check are in cells
N1:Q1.

=IF(OR(N1=0,ISBLANK(N1)),IF(OR(O1=0,ISBLANK(O1)),IF(OR(P1=0,ISBLANK(P1)),IF(OR(Q1=0,ISBLANK(Q1)),"",Q1),P1),O1),N1)

Hope this helps.

Thanks,
Bill Horton

 

[Mark]

Sorry, got ahead of myself. If you want M1 to be blank if there are no
qualifying values (0,null) in N1:Q1 then use this instead of what I
said last:=IF(SUBTOTAL(4,N1:Q1)=0,"",SUBTOTAL(4,N1:Q1))
That should do it!

 

[Guest]

This sounds great except that I don't want to use the maximum value across
the rows, I need to use them in the order of priority (N, then O, then P,
then Q). But I'm sure I'll sure this another time! Thanks.

 

[Guest]

Bill, this works perfectly!!! Thank you so much.

Gretta