IF calculation problem
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: IF calculation problem

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am new to excel and am trying to us the IF function to do a calculation.
    The calculation will be done in D3 What I want to do is if the letter "I" is typed in B3 it should divide the value that is entered in C3 by 2.5. Or if the letter "D" is typed in B3 it should divide the value that is entered in C3 by 1.5. Is anything like this possible?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure, the formula that goes in D3 should say:

    =if(b3="i",c3/2.5,if(b3="d",c3/1.5,0))

    HTH

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Dallas, TX
    Posts
    316
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am new to excel and am trying to us the IF function to do a calculation.
    The calculation will be done in D3 What I want to do is if the letter "I" is typed in B3 it should divide the value that is entered in C3 by 2.5. Or if the letter "D" is typed in B3 it should divide the value that is entered in C3 by 1.5. Is anything like this possible?


    try =IF(B3="I",C3/2.5,IF(B3="D",C3/1.5,))

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks works great!!

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    Do calculations but if criteria not present
    put blank into the cell ("").

    =IF(B3="I",C3/2.5,IF(B3="D",C3/1.5,""))

    Do calculations but if criteria is not True,
    put 0 in the cell.
    Use either of the following.

    =IF(B3="I",C3/2.5,IF(B3="D",C3/1.5,0))

    =(B3="I")*C3/2.5+(B3="D")*C3/1.5

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is a different question than the previous but I typed the following in cell C21 and it says "#VALUE!"
    =IF(E3:E17="Pioneer 33r87",B21-D3:D17)
    What I would like to do is if the text "pioneer 33r87" is found in cells E3 through E17 then take the number entered in the cell that is to the left of it i.e. D3 and subtract that amount from cell B21. What would the correct formula look like. Thanks.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    =B21-SUMPRODUCT((E3:E17="Pioneer 33r87")*(D3:D17))

    or use cell reference for criteria
    =B21-SUMPRODUCT((E3:E17=E3)*(D3:D17))

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You guys are awesome! Again it works great.

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    After looking at this spreadsheet more I keep finding ways to make it better. For example the name in cell A21 is what is to be looked for in E3:E17. How can I get the below formula to changed the E3:e17 search when a new name is typed in A21 and still perform the same calculation? Thanks

    =B21-SUMPRODUCT((E3:E17="Pioneer 33r87")*(D3:D17))

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    Please read above suggestion

    or use cell reference for criteria
    =B21-SUMPRODUCT((E3:E17=E3)*(D3:D17))

    Change the E3 to cell that contains your criteria!

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
  •  

 

 
DMCA.com