+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : IF Super formula

  1. #1
    Registered User
    Join Date
    07-16-2012
    Location
    New Philadelphia, OH
    MS-Off Ver
    Excel 2007
    Posts
    5

    IF Super formula

    I am attempting to create a IF function that will give me 4 possible results.

    Between 1000 and 524, 0 and 224 should result in "R"
    Between 525 and 501, 225 and 249 should result in "LS"
    Between 500 and 438, 250 and 312 Should Result in "FT"
    Between 313 and 436 should result in "A"

    This was my first hasty attempt
    =IF(AND(F13<0,F13<225),IF(AND(F13>224,F13<250),IF(AND(F13>249,F13<313),IF(AND(F13>312,F13<438),IF(AND(F13>437,F13.501),IF(AND(F13>500,F13<526),IF(AND(F13>525),"R"),"L/S"),"F/T"),"A"),"F/T"),"L/S"),"R")

    and the start of my Second
    =IF(F13>1000,IF(F13<526,"R"))

    the second one works until I add more fields.

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,980

    Re: IF Super formula

    You appear to have some gaps and overlaps there, plus it's not clear what happens if it's over 1000, but something like
    =IF(F13<>"",LOOKUP(F13,{0,"R";225,"LS";250,"FT";313,"A";437,"FT";501,"LS";525,"R"}),"")
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    07-16-2012
    Location
    New Philadelphia, OH
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: IF Super formula

    Thank You very much it appears to work well, i have to punch in some more numbers to double check tho, that also appears to work well for my other field tolerances too. Again Thank You very much.

  4. #4
    Registered User
    Join Date
    07-16-2012
    Location
    New Philadelphia, OH
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: IF Super formula

    Now is there any way i could expand that to accept other tolerances from other cells. sort of like a filter, it has to meet B1 criteria, B2, B3, B4, B5, And B6 to achieve A, FT, LS, or R. I know there is a way maybe with an AND, but I'm not sure where to plug them in.

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,980

    Re: IF Super formula

    Can you elaborate on that? A sample workbook would be useful.

  6. #6
    Registered User
    Join Date
    07-16-2012
    Location
    New Philadelphia, OH
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: IF Super formula

    after it sorts that, there are ranges in columns K through R 13. i cant disclose all of them but K13 is 0- 7,061 should be R, 7,062-7,093 should be LS, 7,093-7,124 should be A. >7,125 R, shouldn't it be,
    =IF(F13<>"",LOOKUP(F13,{0,"R";225,"LS";250,"FT";313,"A";437,"FT";501,"LS";525,"R"}),=IF(F13<>"",LOOKUP(F13,{0,"R"7,021,"LS";7,092,"A";7,124,"R"}),"")"")

    do i need to have the first set of brackets share all the information.
    i hope this helps.

  7. #7
    Registered User
    Join Date
    07-16-2012
    Location
    New Philadelphia, OH
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Excel 2007 : IF Super formula

    Here You Go
    i hope this helps more
    Attachment.xlsx

+ 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