Thanks:  0
Likes:  0

# Thread: How to do a complex if and formula in excel

1. ## How to do a complex if and formula in excel

I want to know how to do the following in excel I've written if and loops but I haven't been able to figure this one out.

I have numbers in column B

If the value in column B are greater then 0 and less than or equal to .25 I want to display the number 1
If the value in column B are greater then .25 but less than or equal to .50 I want to display the number 2
If the value in column B are greater then .50 but less than or equal to .75 I want to display the number 3
If the value in column B are greater then .75 but less than or equal to 1.00 I want to display the number 4

2. ## Re: How to do a complex if and formula in excel

=IF(OR(B1="",B1>1),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))

I did not take into account negative numbers but I hope you will not enter them
but if this is going to happen
=IF(OR(B1="",B1>1,B1<1),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))

3. ## Re: How to do a complex if and formula in excel

An alternative approach, if you are 100% certain that the values will ALWAYS be between 0 and 1.

=roundup(B1/.25,0)

4. ## Re: How to do a complex if and formula in excel

Originally Posted by Robert Mika
I did not take into account negative numbers but I hope you will not enter them
but if this is going to happen
=IF(OR(B1="",B1>1,B1<1),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))
You probably meant =IF(OR(B1="",B1>1,B1<0),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))

5. ## Re: How to do a complex if and formula in excel

Originally Posted by 1968rm
You probably meant =IF(OR(B1="",B1>1,B1<0),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))
Yes.
Thank you for the correction.

## User Tag List

#### Posting Permissions

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