# IF conditions

1. ## IF conditions

Hi, I want to write conditions in a column and I was told to use Excel functions but there is a limit to conditions. Here is the problem:

There is a column of numbers (A) and I want to put conditions in the corresponding cells in column B. The conditions are:

IF 0<Ai<100 then write 50
otherwise IF 100<Ai<150 then write 52
otherwise IF 150<Ai<200 then write 54
...
...
...
...
I have 70 ranges and therefore 70 conditions.The conditions don't follow a pattern. The beginning of the range is 0 and the end is 8000. There are 1536 rows (A2 to A1538). I just want to know how to write several IF conditions after each other. The below are some ranges I want to write:

34-100 50.0
100-150 52.0
150-200 55.0
200-250 58.0
250-300 61.0
300-350 64.0
350-400 67.0
400-450 70.0
450-500 73.0
500-550 76.0
550-600 79.0
600-650 82.0
650-700 85.0
700-750 88.0
750-800 91.0
800-850 94.0
850-900 97.0
900-950 98.5
950-1000 100.0
1000-1100 100.0
1100-1200 102.5
1200-1300 105.0
1300-1400 107.5
1400-1500 110.0
1500-1600 112.5
1600-1700 115.0
1700-1800 117.5
1800-1900 119.0
1900-2000 120.0
2000-2100 120.0
2100-2200 122.5
2200-2300 125.0
2300-2400 127.5
2400-2500 130.0
2500-2600 132.5
2600-2700 135.0
2700-2800 137.5
2800-2900 139.0
2900-3000 140.0
.
.
.
.
Thanks a lot for Helping

2. ## Re: IF conditions

Set up a 2-column table somewhere that summarises those 70 conditions, like this:

0 .......50
100....52
150 ...54
200 ...

and so on. Note that the first column is the bottom of the range that gives rise to the value in the second column. Suppose this table occupies X1 to Y70, then you can use this formula in B1:

=VLOOKUP(A1,\$X\$1:\$Y\$70,2)

and then copy down as far as you need to.

Hope this helps.

Pete

3. ## Re: IF conditions

Thanks a lot Pete and Richard, I've also written some codes myself,(for 5 rows and 3 conditions) but when I run it, It says "Compile error: Else without if". Would you please tell me if I can use this code,

Sub Audit()
Dim i As Integer
For i = 1 To 5
If Cells(i, 1) >= 34 And Cells(i, 1) < 100 Then Cells(i, 2) = 50
ElseIf Cells(i, 1) >= 100 And Cells(i, 1) < 150 Then Cells(i, 2) = 52
ElseIf Cells(i, 1) >= 150 And Cells(i, 1) < 200 Then Cells(i, 2) = 54
Else: Cells(i, 1) = 0
End If
Next
End Sub

Thanks again.

4. ## Re: IF conditions

I was told to use Excel functions
so why are you trying to use vba?

5. ## Re: IF conditions

Originally Posted by martindwilson
so why are you trying to use vba?
Hi Martindwilson, Because I have 70 conditions and there is a limit to number of conditions used in Excel IF function.

6. ## Re: IF conditions

use pete's suggestion it works just fine

7. ## Re: IF conditions

Pete, Richard and martindwilson I used your solution and it worked perfectly, Thank you very much for helping.

8. ## Re: IF conditions

Okay, thanks for the rep.

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1