Something simpler than many nested [IF-Then-Else] commands????

[Clint]

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.

The attached Excel file has actual data from one of our experiments. I put a yellow background color on the cells that are relevant to my question. At the bottom of the table of data, I wrote descriptions of the logic that is required. I hope the descriptions are clear. I think/hope Excel has formulas that will provide a simple solution to what I'm trying to achieve.

I'm using Excel 14.2.5 on a Mac. The worksheet must work on both Mac and Windows machines. I'm hoping to do this without using VBA.

Thanks in advance for any suggestions.

[Ken Puls]

The column K one is fairly easy. IN K2: =IF(AND(ISNUMBER(B2),NOT(ISBLANK(B2))),1,10)

With regards to column J, I"m sure there is an elegant way, but to be honest, I'd probalby just go with the nested IF statements. Yes, it will be ugly, but it's probably going to be a lot easier to build and maintain than an elegant formula. (Having said that, Bob Phillips will probably come along and post a really short version to accomplish it.

[Clint]

Thanks Ken! Here are two different formulas for the J column that I got from excelforum.com (is it bad protocol to mention another forum on your forum?)

=INDEX(B2:I2,MIN(IF(ISNUMBER(B2:I2),IF((B2:I2>0)*(B2:I2=INT(B2:I2)),COLUMN(B2:I2)-1))))*10^INDEX(B$1:I$1,MIN(IF(ISNUMBER(B2:I2),IF((B2:I2>0)*(B2:I2=INT(B2:I2)),COLUMN(B2:I2)-1))))


=IF(SUM(B2:I2)=0,IF(COUNT(B2:I2)=0,"",0),INDEX(2:2,,MIN(IF((B2:I2>0)*ISNUMBER(B2:I2),COLUMN($B:$I),"")))*10^INDEX($1:$1,,MIN(IF((B2:I2>0)*ISNUMBER(B2:I2),COLUMN($B:$I)))))
Both responders also gave formulas for the K column, but yours is the simplest by far.

Thanks again!

[Ken Puls]

No, not bad protocol at all. In fact, we actually kind of encourage it if you post on multiple sites. (Have a read of this article to see why.)

Thanks for posting back the solution!