If (M1>0,M1)

  • Thread starter Thread starter yovation
  • Start date Start date
Y

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
 
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
 
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)
 
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.
 
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
 
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 ( "" )?
 
Note:

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


Gord Dibben MS Excel MVP
 
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
 
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).
 
Back
Top