Results 1 to 4 of 4

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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!

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    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!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •