Solved

# Multiple if and or statements

Posted on 2011-10-17
162 Views
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!

Example.xls
0
Question by:spudmcc

LVL 50

Expert Comment

Try this formula in C2 copied down

=LOOKUP(ABS(A2-B2)*1440,D\$2:F\$6)

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
0

LVL 24

Expert Comment

Not sure but is this what you want?

=IF(A2>B2,"<0",LOOKUP(1440*ABS(A2-B2),\$D\$2:\$D\$6,\$F\$2:\$F\$6))
0

LVL 50

Accepted Solution

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

=IF(A2>B2,"<0",LOOKUP(1440*(B2-A2),\$D\$2:\$F\$6))

regards, barry
0

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.
0

LVL 12

Expert Comment

Another alternative:

=INDEX(\$F\$2:\$F\$6,MATCH(ABS(A2-B2)*1440,\$D\$2:\$D\$6,1))
0

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)
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

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.

#### Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!