+ Reply to Thread
Results 1 to 6 of 6

Formula help - multiple conditions for IF function

  1. #1
    Registered User
    Join Date
    01-10-2016
    Location
    Brisbane, Austalia
    MS-Off Ver
    2013
    Posts
    2

    Wink Formula help - multiple conditions for IF function

    Hi
    I have a spreadsheet that is updated daily, it is used to track hours/kms of trucks and when the last service was completed.

    I need a formula added into column G (HRS truck service due) that will do the following
    - if column E "Last service type" equals "A", add D2(HR's/KMs till truck service due)+250
    - if column E is "B", add D2+125
    - if column E is "C", add D2+125

    For example in the attached worksheet G2 will equal 29,398. I want a formula that will add these values automatically. I did try the IF function (and nesting IF Functions) and I don't think it was correct

    Thank you!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,044

    Re: Formula help - multiple conditions for IF function

    Hi, welcome te the forum

    Did not (yet) look at your file, but may be...
    =D2+125*if(E2="A",2,1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,641

    Re: Formula help - multiple conditions for IF function

    If "A", plus 250, else, plus 125?

    =D2+125+(TRIM(E2)="A")*125
    Quang PT

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,674

    Re: Formula help - multiple conditions for IF function

    Here is the formula you want to use in G2 and copy down, except the values in E are not exactly what you said. Here is the formula based on your description:

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


    This allows for a fourth case where there is an error in E, and it is neither "A" nor "B" nor "C". In such a case the result will be -999 to get your attention.

    However, the data you have shows " A" with a space, not "A". It's better to fix that at the source, but the list you are using is in a different file so I can't change it. Here is a formula that assumes you don't fix it.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  5. #5
    Registered User
    Join Date
    01-10-2016
    Location
    Brisbane, Austalia
    MS-Off Ver
    2013
    Posts
    2

    Re: Formula help - multiple conditions for IF function

    Quote Originally Posted by 6StringJazzer View Post
    Here is the formula you want to use in G2 and copy down, except the values in E are not exactly what you said. Here is the formula based on your description:

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


    This allows for a fourth case where there is an error in E, and it is neither "A" nor "B" nor "C". In such a case the result will be -999 to get your attention.

    However, the data you have shows " A" with a space, not "A". It's better to fix that at the source, but the list you are using is in a different file so I can't change it. Here is a formula that assumes you don't fix it.

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


    Thank you Jeff! This worked! I also removed the space after the "A". Ooooops - rookie error! Thank you everyone for all of your help/suggestions

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,674

    Re: Formula help - multiple conditions for IF function

    If you are using a data validation list with only A, B, and C, then you don't really need the error check. And if you fix the extra space, you could use a simplified version.

    Although the other members' formulas work every bit as well, I had a very specific reason for writing it the way I did. The fact that 250 is 2*125 may be a coincidence, and I avoid taking advantage of coincidences in programming logic. However, I will admit that the fact that B and C correspond the same value may also be a coincidence. So the most loosely coupled formula is

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


    This makes it obvious what is happening and which value corresponds to each of A, B, and C. This way if any of the values change later, it is very easy to update the formula. It is not elegant, compact, or clever, but it is very easy to read, understand, and update.

    (P.S. It is not necessary to quote the entire post that you are responding to. In this case you didn't really need to quote at all. We have a "rule" about that but it's really more of a guideline.)

+ 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. multiple conditions which function to use
    By bbis21 in forum Excel General
    Replies: 7
    Last Post: 11-20-2014, 09:42 PM
  2. MID Function with multiple conditions.
    By kaminanirav in forum Excel General
    Replies: 8
    Last Post: 08-28-2014, 11:56 AM
  3. [SOLVED] IF Function with multiple conditions
    By Rob.Marchel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-22-2014, 01:27 AM
  4. [SOLVED] IF function help, multiple conditions
    By wantucce in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-20-2014, 12:22 PM
  5. [SOLVED] IF function with multiple conditions
    By nicko54 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-10-2013, 01:51 AM
  6. Multiple conditions for if function
    By jiyan200 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-12-2012, 11:12 AM
  7. Looking for a function for multiple conditions
    By MrSales in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2007, 03:27 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