+ Reply to Thread
Results 1 to 13 of 13

If with multiple arguments

  1. #1
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    2013
    Posts
    64

    If with multiple arguments

    Hi,

    I am looking for help on an IF statement where I would like the specific customer number to determine which sales team pot it goes into -

    Column C has a formula that determines what team pot it goes into but I need to adjust current formula to reflect -

    Code in column G starts with "CP" then goes to "INTRA"
    Code in column G starts with "CS" then goes to "INTER"
    If column M shows as "FHA" after the first 2 arguments then populate column C as "FHA"
    IF column M is not ANY of the values as above then it should be classed as "TRADE"

    Original formula assigned companies to 3 separate pots -
    =IF(LEFT(G2,2)="CP","INTRA",IF(LEFT(G2,2)="CS","INTER",IF(G2<>"","TRADE","")))

    Any help would be appreciated.

    Thanks in advance.
    Attached Files Attached Files
    Last edited by BigErnKingpin; 08-01-2019 at 08:30 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,190

    Re: If with multiple arguments

    Ostensibly this:

    =IF(LEFT(G2,2)="CP","INTRA",IF(LEFT(G2,2)="CS","INTER",IF([@[Cost Designator on Main Ord/Network]]="FHA","FH",IF(G2<>"","TRADE",""))))

    However, I don't think that's what you mean.

    Can you tell us ONE cell in column C that should be returned as FHA and why?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: If with multiple arguments

    Try:

    =IF(AND(OR(LEFT(G2,2)="CP",LEFT(G2,2)="CS"),M2="FHA"),"FHA",IF(LEFT(G2,2)="CP","INTRA",IF(LEFT(G2,2)="CS","Inter","Trade")))
    Attached Files Attached Files
    Glenn



  4. #4
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    2013
    Posts
    64

    Re: If with multiple arguments

    Hi Ali,

    In the attached spreadsheet there is only one row where it is classed as FHA (Row 8). To be honest I am 2 days into a new role and have had very little explanation as to the data source or why the records seem so confused. I have literally just been told to amend an existing formula to meet the rules listed above. I have a further meeting later re this and hope to get further clarification but as of just now I am working with basic info. So any help at all is very much appreciated.

    Thanks

  5. #5
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    2013
    Posts
    64

    Re: If with multiple arguments

    Hi Glenn,

    This seems to be working a treat. I will leave this post open until I can confirm for definite however and close it later once I know.

    I really appreciate the help!

    Thanks

  6. #6
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: If with multiple arguments

    On the assumption that it's OK... you're welcome. If not, shout.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,190

    Re: If with multiple arguments

    You haven't fully answered my question, actually.

    So WHY is row 8 FHA and NOT INTER? In what circumstances should FHA trump INTER, or any other category?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,190

    Re: If with multiple arguments

    Ignore my previous post - it seems that Glenn worked it out.

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: If with multiple arguments

    Because it meets both the "either CP or CS" criterion (G) and the "FHA" criterion (M).

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,190

    Re: If with multiple arguments

    Yes, you worked it out - I thought that's what he was getting at, but it wasn't 100% clear (to me).

  11. #11
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: If with multiple arguments

    Nor to me, tbh. I had the IFs the wrong way round at first and noticed that there were no FHAs in the answers. So, I guessed that one HAD to be there.

  12. #12
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    2013
    Posts
    64

    Re: If with multiple arguments

    @AliGW @Glenn thanks both of you for answering so quickly. I will see if this is what my new boss was meaning and then close this post asap, but if not then I shall come back and clarify further.

    I will add rep for both for your help.
    Last edited by BigErnKingpin; 08-01-2019 at 06:58 AM.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,190

    Re: If with multiple arguments

    That's kind of you. Yes, leave the thread open until you are sure, but I think that's what you need.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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