If (M1>0,M1)

[yovation]

Hi,

I'm drawing a blank.....

Function in (N1) : =IF(M1 > 0, then M1) ELSE If(L1 > 0, then L1)
ELSE IF(K1 > 0, K1) etc......

Thank you.

Yovation

 

[David Biddulph]

=IF(M1>0,M1,IF(L1>0,L1,IF(K1>0,K1,"")))

 

[Gary''s Student]

In N1 enter:

=lpv(A1:M1)

and enter this UDF:

Function lpv(r As Range) As Variant
lpv = ""
For Each rr In r
If rr.Value > 0 Then
lpv = rr.Value
End If
Next
End Function

The UDF returns the first positive value starting from M1, working backwards
toward A1

 

[Ron Coderre]

Try this:

N1:
=IF(COUNTIF(J1:M1,">0"),INDEX(1:1,MAX(INDEX((J1:M1>0)*COLUMN(J1:M1),0))),"na")

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[yovation]

Thank you everyone for your help.

I ended up using:
=IF(M1>0,M1,IF(L1>0,L1,IF(K1>0,K1,"")))

It works fine for what I need.

Yovation.

 

[yovation]

Seems like something is wrong here.

I am using this in H1:
=IF(G2>0,G2,IF(F2>0,F2,IF(E2>0,E2,IF(D2>0,D2,IF(C2>0,C2,"")))))

I am working with TEXT (and maybe that is the problem).

anyway, if C2=Hi, H1 is blank?

Can someone try this formula please?

Thank you.
David

 

[RagDyer]

The formula will work with text, BUT, the question is ... what do you have
in the rest of the cells?

Do they perhaps contain formulas that maybe return nulls ( "" )?

 

[Gord Dibben]

Note:

You can nest only 7 IF's so if your "etc." exceeds that you will need something
else.


Gord Dibben MS Excel MVP

 

[yovation]

Hi,

It is the formating, but I don't know how to fix it?

Using the formula
in H1:
=IF(G2>0,G2,IF(F2>0,F2,IF(E2>0,E2,IF(D2>0,D2,IF(C2>0,C2,"")))))


if there is data in C1, D1 is preventing that data from showing up in
H1? So if I delete whatever is invisible in D1, it works. I have
tried to just copy the formating from one cell to another but that
doesn't work. So Excel thinks there is something in D1 but it's not
visible.

Thank you.
David

 

[David Biddulph]

It's not the format of the cell that affects the result of the formula, it's
the content (even if it's only a space).