+ Reply to Thread
Results 1 to 8 of 8

IF conditions

  1. #1
    Registered User
    Join Date
    07-11-2013
    Location
    Tehran
    MS-Off Ver
    Excel 2003
    Posts
    14

    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. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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. #3
    Registered User
    Join Date
    07-11-2013
    Location
    Tehran
    MS-Off Ver
    Excel 2003
    Posts
    14

    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. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: IF conditions

    I was told to use Excel functions
    so why are you trying to use vba?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    07-11-2013
    Location
    Tehran
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: IF conditions

    Quote Originally Posted by martindwilson View Post
    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. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: IF conditions

    use pete's suggestion it works just fine
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-11-2013
    Location
    Tehran
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: IF conditions

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

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: IF conditions

    Okay, thanks for the rep.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Vlookup - Country conditions (Multiple conditions)
    By dluhut in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2013, 11:42 AM
  2. [SOLVED] Text results with two conditions (lookup with two conditions)
    By Davzx in forum Excel General
    Replies: 8
    Last Post: 05-25-2012, 03:08 AM
  3. convert the if conditions to OR conditions
    By nynamyna in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-09-2011, 06:41 PM
  4. Sum with two conditions
    By BannerBrat in forum Excel General
    Replies: 2
    Last Post: 08-11-2005, 05:05 PM
  5. [SOLVED] How to multiple conditions to validate more than 2 conditions to .
    By Bhuvana Govind in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2005, 04:06 PM

Bookmarks

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