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

# 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
1 Solution

Commented:
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

Commented:
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

Commented:
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

Commented:
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

Chief EngineerCommented:
Another alternative:

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

Author 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

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