Something simpler than a lot of nested [IF-THEN-ELSE] commands

[Clinto]

Hi - this is my first time posting.

I'm automating a worksheet for analyzing raw data from biology experiments. I can do most of the automating - it's pretty simple Excel formulas. But there are two steps that I'm having a bit of trouble with. I think I could accomplish what I need with a whole bunch of nested IF-THEN-ELSE commands, but I'm hoping there is a simpler way to do it.

I have an Excel file that illustrates and explains what I need to do, but I can't see a way to upload the file with this post. Is there I way to do that?


(BTW - I'm posting this to a few different Excel forums. They all have very similar formats. If I inadvertently made a duplicate posting, I apologize.]

 

[Aladin Akyurek]

Hi - this is my first time posting.

I'm automating a worksheet for analyzing raw data from biology experiments. I can do most of the automating - it's pretty simple Excel formulas. But there are two steps that I'm having a bit of trouble with. I think I could accomplish what I need with a whole bunch of nested IF-THEN-ELSE commands, but I'm hoping there is a simpler way to do it.

I have an Excel file that illustrates and explains what I need to do, but I can't see a way to upload the file with this post. Is there I way to do that?


(BTW - I'm posting this to a few different Excel forums. They all have very similar formats. If I inadvertently made a duplicate posting, I apologize.]

A long chain IFs can often be implemented as a look up formula that examines a table.

 

[Clinto]

Hi Aladin,

Thanks for the quick reply. Can you point me to an article that explains how to create a look-up formula that examines a table?

In my case, I need to distinguish between empty cells, cells with text and cells with integers. I need to look at a row of cells from left to right, and find the first cell that has a positive integer. Sometimes that will be the first cell in the row. Or sometimes there will be several cells that are empty or have text before the first cell with an integer.

Thanks again!

 

[Aladin Akyurek]

Hi Aladin,

Thanks for the quick reply. Can you point me to an article that explains how to create a look-up formula that examines a table?

In my case, I need to distinguish between empty cells, cells with text and cells with integers. I need to look at a row of cells from left to right, and find the first cell that has a positive integer. Sometimes that will be the first cell in the row. Or sometimes there will be several cells that are empty or have text before the first cell with an integer.

Thanks again!

Given A2:I2...
[TABLE="width: 432"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]-2
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]3.2
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]0.9
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]P
[/TD]
[/TR]
</tbody>[/TABLE]

First empty (unused) cell:

=MATCH(TRUE,ISBLANK(A2:I2),0)

First blank (used or unused) cell:

=MATCH(TRUE,A2:I2="",0)

First non-blank cell:

=MATCH(TRUE,A2:I2<>"",0)
=MATCH(1,1-(A2:I2=""),0)
=INDEX(A2:I2,MATCH(TRUE,A2:I2<>"",0))
=INDEX(A2:I2,MATCH(1,1-(A2:I2=""),0))

First text cell:

=MATCH(TRUE,ISTEXT(A2:I2),0)
=INDEX(A2:I2,MATCH(TRUE,ISTEXT(A2:I2),0))

First integer cell:

=MATCH(1,IF(ISNUMBER(A2:I2),IF(A2:I2=TRUNC(A2:I2),1)),0)
=INDEX(A2:I2,MATCH(1,IF(ISNUMBER(A2:I2),IF(A2:I2=TRUNC(A2:I2),1)),0))

First positive integer cell:

=MATCH(1,IF(ISNUMBER(A2:I2),IF(A2:I2>0,IF(A2:I2=TRUNC(A2:I2),1))),0)
=iNDEX(A2:I2,MATCH(1,IF(ISNUMBER(A2:I2),IF(A2:I2>0,IF(A2:I2=TRUNC(A2:I2),1))),0))

All of the foregoing formulas need to be confirmed with control+shift+enter, not just with enter.

If this does not answer your needs, please post the specifics of the problem you are trying to cope with. Provide also a small sample if possible.

 

[Clinto]

Hi Aladin - thanks! That is very helpful. I appreciate you taking the time to provide example formulas.

 

[Aladin Akyurek]

Hi Aladin - thanks! That is very helpful. I appreciate you taking the time to provide example formulas.

You are welcome. Thanks for providing feedback.