+ Reply to Thread
Results 1 to 9 of 9

Need help with multiple if and is there a way to consolidate all exceptions?

  1. #1
    Registered User
    Join Date
    10-01-2017
    Location
    USA
    MS-Off Ver
    10
    Posts
    46

    Need help with multiple if and is there a way to consolidate all exceptions?

    Hi Folks -

    I need to write a rather large IF statement. I"m 75% of the way there however I need to add another exception. The caveat is, the additional exception consists of 26 additional exceptions.

    Allow me to explain.

    If cell in Column T > 0, I then determine what country I am dealing with by checking the prefix of the cell in Column A. Based on that, I divide the value in Column T by the country specific number to determine the the FTE figure - this part is working great.

    The caveat is, if the cell in Column T equals 0, I need to then go to the cell in Column AG to determine the Host Country, then based on what is returned, divide the value in Column AL by the number which represents that Host Country.

    As you can see, there are 27 countries, so each part of that If (if Column T = 0) I need to repeat this exception 26 times to capture all potential countries.

    Is there an easier way to do this?

    Thank you!
    Attached Files Attached Files

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

    Re: Need help with multiple if and is there a way to consolidate all exceptions?

    is there a reason that you have the T>0 in the formula? In all of the data you gave T is never less than 0. If this is consistent throughout then it would seem that part could be removed.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    10-01-2017
    Location
    USA
    MS-Off Ver
    10
    Posts
    46

    Re: Need help with multiple if and is there a way to consolidate all exceptions?

    Well correct, but I need to act upon Column T if the value is actually 0 as well.

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

    Re: Need help with multiple if and is there a way to consolidate all exceptions?

    I would build a table, I'm working the details now and will upload it when finished, I'd refer to the table with a vlookup or index/match.

  5. #5
    Registered User
    Join Date
    10-01-2017
    Location
    USA
    MS-Off Ver
    10
    Posts
    46

    Re: Need help with multiple if and is there a way to consolidate all exceptions?

    That sounds promising! Thank you very much!!

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

    Re: Need help with multiple if and is there a way to consolidate all exceptions?

    see the attached. You could hide the helper column (in col AU) and the table (in AW and AX) or put them on a different tab.
    so I used the left function for the values in column AU, consolidated the values in AW and your divisors in col AX.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-01-2017
    Location
    USA
    MS-Off Ver
    10
    Posts
    46

    Re: Need help with multiple if and is there a way to consolidate all exceptions?

    HI Sambo -

    Wow thank you so much!!! This is working like a charm! However, I need to solve for my exception.

    If Column T is 0, I then need to determine Host Country from Column AG and then do the math accordingly in a similar fashion. I added a second VLOOKUP piece but it doesn't seem to fire.

    Attached is my updated workbook if you could take a peek. Thank you again for all of our help!
    Attached Files Attached Files

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

    Re: Need help with multiple if and is there a way to consolidate all exceptions?

    I think this is what you want...
    =IF(T5<>0,T5/VLOOKUP(AU5,$AW$5:$AX$31,2,FALSE),IF(T5=0,AL5/VLOOKUP(AV5,$AW$5:$AX$31,2,FALSE),"not a valid country"))

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Need help with multiple if and is there a way to consolidate all exceptions?

    An alternative in U5:

    =IF(T5="","",IFERROR(IF(T5=0,AL5,T5)/(VLOOKUP(IF(T5=0,LEFT(AG5,3),LEFT(A5,3)),$AW$5:$AX$31,2,FALSE)),"Not a valid country"))

    This does away with the need for the helper columns in AU and AV.

    Hope this helps.

    Pete

+ 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. Merging Multiple Cells Into One Cell with exceptions
    By dublisto in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-05-2014, 09:48 PM
  2. [SOLVED] Consolidate all data in multiple worksheets of multiple workbooks in one Master file.
    By adil.master in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2014, 09:59 PM
  3. Comparing Multiple Columns on 2 sheets and report Exceptions on 3rd Sheet
    By KrystalQ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2014, 11:41 AM
  4. How to use multiple functions with multiple exceptions
    By Microsoftnovice in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2012, 01:15 PM
  5. multiple worksheets, find exceptions and merge into new worksheet
    By letsxcel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-25-2012, 03:55 PM
  6. Consolidate from multiple workbooks
    By delt127 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-06-2011, 12:44 PM
  7. List File Name with multiple exceptions
    By AdamParker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2008, 11:59 AM

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