Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: IF Statement errors

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    TX
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to use the following IF statement, which I want to look at a cell & return varying percentage amounts depending on the amount in the cell being looked at.

    Ex. if F4 contains 10795, I want F5 to reflect 0.05; if F4 contains 75000, I want F5 to reflect 0.03, etc...

    I keep getting an error when I attempt to use this IF statement. Any ideas? Is it the "AND" causing the problem? How else I can make it return the desired amount for the ranges needed?

    My complete list of ranges & desired percentages is as follows, but since the IF statement must be limited to 7, I eliminated the 1st set:

    1-5000 = 0.07 (or minimum 500)
    5001-10000 = 0.06
    10001-25000 = 0.05
    25001-35000 = 0.04
    35001-40000 = 0.035
    40001-75000 = 0.03
    75001-150000 = 0.025
    150001-UP = 0.02

    =IF(F4>5000AND<10001,0.06,IF(F4>10000AND<25001,0.05,IF(F4>25000AND<35001,0.04,IF(F4>35000AND<40001,0.035,IF(F4>40000AND<75001,0.03,IF(F4>75000AND<150001,0.025,IF(F4>150000,0.02)))))))

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Change your 'And' Syntax
    =IF(AND(F4>5000,F4<10001),0.06,IF(AND(F4>10000,F4<25001),0.05,IF(AND(F4>25000,F4<35001),0.04,IF(AND(F4>35000,F4<40001),0.035,IF(AND(F4>40000,F4<75001),0.03,IF(AND(F4>75000,F4<150001,0.025),IF(F4>150000,0.02)))))))
    throughout your formula for the correct syntax, I think.
    Tom

    [ This Message was edited by: TsTom on 2002-04-23 15:36 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-23 15:22, langumd wrote:
    I am trying to use the following IF statement, which I want to look at a cell & return varying percentage amounts depending on the amount in the cell being looked at.

    Ex. if F4 contains 10795, I want F5 to reflect 0.05; if F4 contains 75000, I want F5 to reflect 0.03, etc...

    I keep getting an error when I attempt to use this IF statement. Any ideas? Is it the "AND" causing the problem? How else I can make it return the desired amount for the ranges needed?

    My complete list of ranges & desired percentages is as follows, but since the IF statement must be limited to 7, I eliminated the 1st set:

    1-5000 = 0.07 (or minimum 500)
    5001-10000 = 0.06
    10001-25000 = 0.05
    25001-35000 = 0.04
    35001-40000 = 0.035
    40001-75000 = 0.03
    75001-150000 = 0.025
    150001-UP = 0.02

    =IF(F4>5000AND<10001,0.06,IF(F4>10000AND<25001,0.05,IF(F4>25000AND<35001,0.04,IF(F4>35000AND<40001,0.035,IF(F4>40000AND<75001,0.03,IF(F4>75000AND<150001,0.025,IF(F4>150000,0.02)))))))
    You don't need to eliminate any category from your percent table. Just enter it as a 2-column list somewhere (in a worksheet called "Admin", for example).

    {1,0.07;
    5001,0.06;
    10001,0.05;
    25001,0.04;
    35001,0.035;
    40001,0.03;
    75001,0.025;
    150001,0.02}

    and select all of these cells, go the Name Box on the Formula Bar, type PList, and hit enter.

    Usage:

    =VLOOKUP(A1,PList,2)

    where A1 is a number for which you want to retrieve appropriate percentage.

    Aladin

    [ This Message was edited by: aladin akyurek on 2002-04-24 00:05 ]

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi langumd:
    Further to suggestions by TsTom and Aladin, here is what might be specific to your usage.
    Add the following table in cells A1:B9

    Qty Percent
    500 0.07
    5001 0.06
    10001 0.05
    25001 0.04
    35001 0.035
    40001 0.03
    75001 0.025
    150001 0.02

    and
    1. Click in cell G4
    2. then INSERT|NAME|DEFINE ... enter Percentage in the box for Names_in_workbook
    3. in Refers_to: box enter the following formula

    =VLOOKUP(F4,$A$1:$B$9,2)

    4. Click on OK

    Now if you enter 10795 in cell F4 and enter the following formula in cell G4

    =Percentage ..... that will result in 0.05

    HTH

    Please post back if it works for you ... otherwise explain a little further and let us take it from there!

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Slightly OT, but what exactly do these percentages represent?

    If I take the maximum amount in each range and multiply it by the %, I have the following results:

    5000 0.07 350
    10000 0.06 600
    25000 0.05 1250
    35000 0.04 1400
    40000 0.035 1400
    75000 0.03 2250
    150000 0.025 3750
    150001 0.02 3000.02

    The maximum at 35K and 40K is the same. If this is the total extra charge to a customer, for example, a purchase of 36K is less than a purchase of 35K.

    So, these cannot be quantity discounts at certain breakpoints. If these are marginal increases, meaning 36K purchased is 4% for the first 35K and 3.5% for the remaining 1K, then you are into a whole different realm here.

    If this is the case, you *must* check Chip Pearson's website and search for "Bracket or Progressive Pricing." It is an excellent tutorial for the progressive pricing model (this also applies to marginal tax rates).

    http://www.cpearson.com/excel/pricing.htm

    If this is not what is needed, my apologies.

    Bye,
    Jay

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good point Jay!
    I did not ask what Diane intended to do with the formula ... on your raising this point, I realize I should have because I agree with you the irrationality of single step pricing structure. in my limited experience, I also believe in having a stepped pricing structure, e.g. the price per pice for first 100, and then the price per piece for next 100, and so on ...

    Regards!

    Yogi Anand

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    TX
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    These numbers will actually be print totals for a flyer mailing. We have 20 different geographic breakouts which have varying customer totals. Based on these customer totals (& the type of breakout...some get the percentage indicated, some get the percentage indicated + increased by 1 percentage point).

    I have pasted a sample of the 3 columns I am working with, customer count (F4:F21), percentage of overs (G4:G21), and Total Number of Overs (H4:H21). The percentages I am trying to input automatically would change according to the customer count; the higher the customer count the less overs I need printed.

    In the meantime, I will try some of your suggestions to see if I can get this to work for me. thanks for your help


    CUSTOMER.....%........#
    COUNT......OVER......OVER
    2,169......0.240.....521
    26,081.....0.040.....1,043
    245,014....0.020.....4,900
    11,720.....0.060.....703
    32,785.....0.050.....1,639
    80,014.....0.035.....2,800
    9,279......0.070.....650
    141,403....0.025.....3,535
    191,338....0.020.....3,827
    82,990.....0.025.....2,075
    33,460.....0.040.....1,338
    28,744.....0.040.....1,150
    120,165....0.025.....3,004
    43,321.....0.040.....1,733
    19,682.....0.060.....1,181
    46,545.....0.040.....1,862
    8,648......0.070.....605
    24,261.....0.060.....1,456


    [ This Message was edited by: langumd on 2002-04-24 07:43 ]

    [ This Message was edited by: langumd on 2002-04-24 07:48 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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