Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: Excel "IF" Statement

    If the numeric value in a given cell falls within a predefined range (500 to 1000), I want the value to be multiplied by a constant value (0.05). If the criteria is not met, I want the cell identified as the recipient of this action to reflect a value of 0.00.

    Would someone please show me what this formula should look like? Certainly would appreciate it.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Immediate solution:
    Assume your target cell is D1, then in E1 put this formula:

    =IF(500<D1<1000,D1*0.05,0.00)

    (to get the two digit display for the "false" portion, you will have to format the cell that way. Right click cell, choose Format Cell, Number (tab), then choose "Number in the left, and two decimals on the right side. Click OK.

    Better Solution

    It might also work better to put the following numbers into these cells and use the corresponding formula:

    A1 = 500
    B1= 1000
    C1 = 0.05
    D1 (target cell)
    E1 (formula)

    =IF(AND(D1>A1)*(D1<B1),D1*C1,0.00)

    The reason for doing it this way is that if you reference these values in several formulas, you only have to change A1, B1, C1, rather than searching through every formula.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2003
    Posts
    2
    Thanks a ton....works great! I really appreciate
    you taking the time to respond.

    Dave





    Originally posted by shades
    Immediate solution:
    Assume your target cell is D1, then in E1 put this formula:

    =IF(500<D1<1000,D1*0.05,0.00)

    (to get the two digit display for the "false" portion, you will have to format the cell that way. Right click cell, choose Format Cell, Number (tab), then choose "Number in the left, and two decimals on the right side. Click OK.

    Better Solution

    It might also work better to put the following numbers into these cells and use the corresponding formula:

    A1 = 500
    B1= 1000
    C1 = 0.05
    D1 (target cell)
    E1 (formula)

    =IF(AND(D1>A1)*(D1<B1),D1*C1,0.00)

    The reason for doing it this way is that if you reference these values in several formulas, you only have to change A1, B1, C1, rather than searching through every formula.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091

    Smile

    Glad that it worked, and happy to help.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    Shades

    this was great - thanks!
    I didn't realize you could compare two values in one if statement in excel.

    ---------------------
    A1 = 500
    B1= 1000
    C1 = 0.05
    D1 (target cell)
    E1 (formula)

    =IF(AND(D1>A1)*(D1<B1),D1*C1,0.00)
    ---------------------

    I have often done nested if statements to get this result.

    I have also tried unsuccessfully to write it this way:
    =IF((D1>A1) AND (D1<B1),D1*C1,0.00)

    Excel 2000 Help doesn't seem to provide any info on syntax for combining such expressions instead of nesting if statements. Do you (or anyone) know any where to get info on excel syntax for this?


    Thanks!

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    Originally posted by mdr02125
    Shades

    this was great - thanks!
    I didn't realize you could compare two values in one if statement in excel.

    ---------------------
    A1 = 500
    B1= 1000
    C1 = 0.05
    D1 (target cell)
    E1 (formula)

    =IF(AND(D1>A1)*(D1<B1),D1*C1,0.00)
    ---------------------

    I have often done nested if statements to get this result.

    I have also tried unsuccessfully to write it this way:
    =IF((D1>A1) AND (D1<B1),D1*C1,0.00)

    Excel 2000 Help doesn't seem to provide any info on syntax for combining such expressions instead of nesting if statements. Do you (or anyone) know any where to get info on excel syntax for this?


    Thanks!
    I bought the book MS Excel 2000 Formulas by John Walkenbach. One of the best investments I have made. You can pick up a used copy on Amazon for about $15.

    700 pages of most types of formulas and combinations; plus expanding into array formulas. And he also shows what you might think "should" work, but doesn't and why. He also includes several chapters on writing UDF (User Defined Functions) through VBA.
    Last edited by shades; 01-09-04 at 18:08.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Originally posted by mdr02125
    Shades

    this was great - thanks!
    I didn't realize you could compare two values in one if statement in excel.

    You can add even more conditions.

    =IF(AND(First test)*(Second Test)*(Third Test),.....)

    You can also nest IF statements within IF statements (up to seven nested IF's - although there are work-arounds for even that).

    And even more powerful formula is SUMPRODUCT, which can do things like summing with multiple conditions (SUMIF can handle only one condition), and even count items based on several criteria. But SUMPRODUCT will cost in terms of processing calculation time if there are many to do.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    BTW, I am still learning this whole thing about Formulas and what can and cannot be done.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    Thanks!
    Attached Thumbnails Attached Thumbnails thankyou anim.gif  

Posting Permissions

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