Multiple if and or statements

Posted on 2011-10-17
Last Modified: 2012-05-12
Hi All!

I have a table that requires us to look at the time variance and assess if it is between X and Y then give us Z.  Also, it is possible that time 1 could be smaller than time 2.  

I've attached a small excel sheet for clarity.  

Thanks so much!  

Question by:spudmcc
    LVL 50

    Expert Comment

    by:barry houdini
    Try this formula in C2 copied down


    If you want to do it without the table

    =LOOKUP(ABS(A2-B2)*1440,{0;16;31;46;61},{"0-15 Minutes";"16-30 Minutes";"31-45 Minutes";"46-60 Minutes";"> 61 Minutes"})

    regards, barry
    LVL 24

    Expert Comment

    Not sure but is this what you want?

    LVL 50

    Accepted Solution

    Actually Stephen, I was just coming round to that myself, presumably you don't need the ABS though, perhaps


    regards, barry
    LVL 24

    Expert Comment

    Barry, indeed not, I began by thinking the direction of difference didn't matter, but then realised probably not so but forgot to remove the ABS.
    LVL 12

    Expert Comment

    Another alternative:


    Author Closing Comment

    Thank you all!  

    The response from barryhoudini was the best fit for my requirements.  I so much appreciate all of your talent, time and patience with us "newbies".  

    spudmcc (Andy)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now