+ Reply to Thread
Results 1 to 8 of 8

IF THEN Formula Help!

  1. #1
    Registered User
    Join Date
    09-09-2014
    Location
    Town of Tonawanda
    MS-Off Ver
    2010 Excel
    Posts
    15

    IF THEN Formula Help!

    I have been working on this for two days now, and frustration is finally setting in.
    This is what I am trying to accomplish: each person in my group receives a specific amount of money depending on the number of years they have been in the group (longevity). In my description, the number of years is X.

    If X is less than 5, receive nothing
    If X is greater than or equal to 5, receive $700
    If X is greater than or equal to 7, receive $900
    If X is greater than or equal to 10, receive $1000
    If X is greater than or equal to 15, receive $1100
    If X is greater than or equal to 20, receive $1200

    I'm trying ones that end up looking something like this:
    =IF(A1>=5,"700",""),IF(A1<=6,"900",IF(A1>=9,"1000") etc. (Doesn't work)

    Anyone have any ideas for me?
    Please help!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: IF THEN Formula Help!

    try =if(a1<5,"",if(a1<7,$700,if(a1<10,$900,if(a1<15,$1000,if(a1<20,$1100,$1200)))) (not tested)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: IF THEN Formula Help!

    Try this out:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: IF THEN Formula Help!

    Try this

    =IFERROR(LOOKUP(A1,{5,700;7,900;10,1000;15,1100;20,1200}),0)
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    09-03-2014
    Location
    Lakewood, Ohio
    MS-Off Ver
    Office 365
    Posts
    12

    Re: IF THEN Formula Help!

    Try this

    =IF(A1<5,0,(IF(A1<7,700,(IF(A1<10,900,(IF(A1<15,1000,(IF(A1<20,1100,1200)))))))))

  6. #6
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: IF THEN Formula Help!

    The Reason:

    =IF(A1>=5,"700",""),IF(A1<=6,"900",IF(A1>=9,"1000")

    Test
    Value if true
    Value if false
    This is out of place/incorrect syntax, and will always result in an error. Also, you have >'s and <'s in there, I'm not sure if that was intentional or not, but they should all go the same way (all greater than or all less than) in this type of formula. See below!



    =IF(logical_test,value_if_true,value_if_false)

    logical_test: This is where you put the test. The rest can be any statement that results in a True or False result, for example A1>15. If A1 is in fact larger than 15, the result will be True. Common operators would be
    .
    • ">" Greater Than
    • "<" Less Than
    • "<>" NOT equal to
    • ">=" Greater than or equal to
    • "<=" Less than or equal to
    • "=" Equal to


    value_if_true: This is the result if logical_test returns a TRUE value.
    value_if_false: As you probably guessed, this is the result if logical test returns a FALSE value.


    A full example of the formula would be:

    =IF(A1=10,A1+10,B1)

    Now, if cell A1 is equal to 10, the result of the formula will be 20. Why? Because if A1=10, the value_if_true will occur, resulting in 10+10
    If A1 is NOT equal to 10, the result of the formula will be whatever the value of B1's cell is

    Remember, any one of the three inputs for the IF function can be another formula, for example, here are two IF's nested:
    =IF(A1=10,A1+10,IF(A1>10,A1,B1-10))
    The value_if_false is another IF function, that will only be executed by Excel if the previous IF statement (A1=10) is False




    And for the hell of it, since everyone is posting solutions, here is another solution using a CHOOSE function (how exotic!):
    =IF(A1="","",IFERROR(CHOOSE(INT(A1),0,0,0,0,700,700,900,900,900,1000,1000,1000,1000,1000,1100,1100,1100,1100,1100,1200),1200))

    I'm unique!
    Last edited by Speshul; 09-09-2014 at 11:59 AM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  7. #7
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: IF THEN Formula Help!

    Try this..
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    you can also use ranges instead of Arrays...
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  8. #8
    Registered User
    Join Date
    01-08-2014
    Location
    Irvine, CA ,USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: IF THEN Formula Help!

    Try this
    =IF(AND(A1>=5,A1<7),"700",IF(AND(A1>=7,A1<10),"900",IF(AND(A1>=10,A1<15),"1000",IF(AND(A1>=15,A1<20),"1100",IF(A1>=20,"1200","")))))

+ 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. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  2. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Tags for this Thread

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