• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Multiple if and or statements

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
spudmcc
Asked:
spudmcc
1 Solution
 
barry houdiniCommented:
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
 
StephenJRCommented:
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
 
barry houdiniCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
StephenJRCommented:
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
 
kgerbChief EngineerCommented:
Another alternative:

=INDEX($F$2:$F$6,MATCH(ABS(A2-B2)*1440,$D$2:$D$6,1))
0
 
spudmccAuthor Commented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now