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 N20, then M2=N2, else M2=O2
If O2 is not null and O20, then M2=O2, else M2=P2
If P2 is not null and P20, then M2=P2, else M2=Q2
If Q2 is not null and Q20, the M2=Q2, else M2=""

Any suggestions? My thanks in advance.
Gretta

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.

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)),I F(OR(P1=0,ISBLANK(P1)),IF(OR(Q1=0,ISBLANK(Q1)),"", Q1),P1),O1),N1)

Hope this helps.

Thanks,
Bill Horton

"G Miller" wrote:

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 N20, then M2=N2, else M2=O2
If O2 is not null and O20, then M2=O2, else M2=P2
If P2 is not null and P20, then M2=P2, else M2=Q2
If Q2 is not null and Q20, the M2=Q2, else M2=""

Any suggestions? My thanks in advance.
Gretta

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!

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.

"Mark" wrote:

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!

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

Gretta

"William Horton" wrote:

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)),I F(OR(P1=0,ISBLANK(P1)),IF(OR(Q1=0,ISBLANK(Q1)),"", Q1),P1),O1),N1)

Hope this helps.

Thanks,
Bill Horton

"G Miller" wrote:

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 N20, then M2=N2, else M2=O2
If O2 is not null and O20, then M2=O2, else M2=P2
If P2 is not null and P20, then M2=P2, else M2=Q2
If Q2 is not null and Q20, the M2=Q2, else M2=""

Any suggestions? My thanks in advance.
Gretta