Need a function to fill column from other columns

[G Miller]

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.

[William Horton]

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

"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 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]

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!

[G Miller]

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!
>
>

[G Miller]

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)),IF(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 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