Another way to do equiv. of embedded =if(...) since no room

[shmizzy]

I have 20 columns of data. On ea. row I am checking from right to left for 1st value that is negative. So I built this:

=(IF((Z10<A10),(Z10),((IF((Y10<A10),(Y10),((IF((X10<A10),(X10),((IF((W10<A10),(W10),((IF((V10<A10),(V10),((IF((U10<A10),(U10),((IF((T10<A10),(T10),((IF((S10<A10),(S10),(0))))))))))))))))))))))))

where a10 holds 0. I want to keep building this to check through col d. but when i build it more where the 0 is I get error. I assuming there is limit on lenght of expression. Looking for a way around this to find first negative # from (r) to (l)?

 

[acw]

Hi

Formula didn't show properly, so I've assumed your 20 columns are G:Z

Try

=INDEX(A40:Z40,MAX(IF(G40:Z40<0,COLUMN(G40:Z40),0)))

Tony

 

[Aladin Akyurek]

Re: Another way to do equiv. of embedded =if(...) since no r

I have 20 columns of data. On ea. row I am checking from right to left for 1st value that is negative. So I built this:

=(IF((Z10 <A10),(Z10),((IF((Y10 <A10),(Y10),((IF((X10 <A10),(X10),((IF((W10 <A10),(W10),((IF((V10 <A10),(V10),((IF((U10 <A10),(U10),((IF((T10 <A10),(T10),((IF((S10 <A10),(S10),(0))))))))))))))))))))))))

where a10 holds 0. I want to keep building this to check through col d. but when i build it more where the 0 is I get error. I assuming there is limit on lenght of expression. Looking for a way around this to find first negative # from (r) to (l)?

=MATCH(TRUE,A10:Z10 < 0,0)

confirmed with control+shift+enter, will return if available the position of the first negative number in A10:Z10.

=INDEX(A10:Z10,MATCH(TRUE,A10:Z10 < 0,0))

confirmed with control+shift+enter, would return if available the first negative number in A10:Z10.

=CELL("Address",INDEX(A10:Z10,MATCH(TRUE,A10:Z10 < 0,0)))

confirmed with control+shift+enter, would return if available the cell reference of the first negative number in A10:Z10.