+ Reply to Thread
Results 1 to 6 of 6

IF Function

  1. #1
    Registered User
    Join Date
    05-11-2010
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007
    Posts
    9

    IF Function

    What I want to do is to use multiple IF statements in the same cell. For instance, lets use data in cells Q4 and T4, returning a value in W4.


    Ok, so, a breakdown of what I want to do (hopefully all in the same cell)..

    If Q4 is =/> 50,000, I need it to take Q4*10% and return that value.

    If Q4 is < 50,000, I need it to look at cell T4, if T4 is greater than 5000, then I need it to subtract 5000-the value in T4, if T4 is less than 5000, then I want it to return a zero.

    To futher complicate things, it can be +5000 or -5000. That is the tolerance -5000 to +5000, I'm searching for anything over or under that.

    I hope someone can help!

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: IF Function

    Hi -

    Not sure I fully understand what you are asking. Your tolerance applies to Q4? In other words If Q4>=55,000 then Q4*0.1? what about numbers that fall within the tolerance range (45,000 - 55,000)? What does the formula do then?

    Based on how I interpret your instructions, the following formula is what I get: =IF(Q4>55000,0.1*Q4,IF(R4<5000,0,Q4-R4))

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: IF Function

    Try

    =IF(Q4>=50000,0.1*Q4,IF(OR(T4>5000,T4<-5000),ABS(T4)-5000,0))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    05-11-2010
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: IF Function

    Thank you Ace_XL!!!!!

  5. #5
    Registered User
    Join Date
    05-11-2010
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: IF Function

    Revision to my formula above...

    If Q4 is =/> 50,000, I need it to take Q4*10% and then look at cell T4, if T4 is over or under (negative or positive) than the (Q4*10%), then return the difference between (Q4*10%) and cell T4. If it isn't over/under then return 0.

    If Q4 is < 50,000, I need it to look at cell T4, if T4 is greater than 5000, then I need it to subtract 5000-the value in T4, if T4 is less than 5000, then I want it to return a zero.

    To futher complicate things, it can be +5000 or -5000. That is the tolerance -5000 to +5000, I'm searching for anything over or under that.

  6. #6
    Registered User
    Join Date
    05-11-2010
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: IF Function

    Revision to my formula above...

    If Q4 is =/> 50,000, I need it to take Q4*10% and then look at cell T4, if T4 is over or under (negative or positive) than the (Q4*10%), then return the difference between (Q4*10%) and cell T4.

    If Q4 is < 50,000, I need it to look at cell T4, if T4 is greater than 5000, then I need it to subtract 5000-the value in T4, if T4 is less than 5000, then I want it to return a zero.

    To futher complicate things, it can be +5000 or -5000. That is the tolerance -5000 to +5000, I'm searching for anything over or under that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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