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

Clint

New member
Joined
Jan 10, 2013
Messages
6
Reaction score
0
Points
1
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.
 

Attachments

  • Example of microbio data worksheet.xlsx
    16.1 KB · Views: 17

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

New member
Joined
Jan 10, 2013
Messages
6
Reaction score
0
Points
1
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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!
 
Top