+ Reply to Thread
Results 1 to 11 of 11

Wanting to string multiple IF(AND) statements together

  1. #1
    Registered User
    Join Date
    03-27-2017
    Location
    Penticton, BC
    MS-Off Ver
    2016
    Posts
    33

    Wanting to string multiple IF(AND) statements together

    I'm brand new on this forum, but an oldie to excel. However I'm completely stumped on this one.

    I have attached the spreadsheet. I have the following formula which is working just fine.

    =IF(AND(A12="Burnaby",C12="Diesel"),0.03046,0.03625)

    But I'd like to string together another IF(And) statement together as I have 3 conditions to be met rather than two.

    Background to the data:
    These are hauling rates. There are 3 conditions that determine price:
    1. The city it was picked up in (there are only two choices - which I think is important to note as maybe it can be simplified to a true false condition)
    2. The city where it is going (this is where it's tricky because there are 5 variables)
    3. If it is gas or diesel (Again there are only two choices - again the true false can maybe work)

    The above formula works if it is going to Penticton from Burnaby and decides the rate based upon gas or Diesel. How do I make it look at all of the variables? should I be throwing an "If(Or)" in there too, everything I've tried errors out on me...

    Or should this be done in VBA? In which case I will also need help - but that's a different forum - I will post the same question over there if we can't figure this out here

    The spreadsheet shows my starter formula, and there is a tab showing the rates (maybe I need to refer back to it in my formula?)

    Any help/tips/tricks would be lovely!

    Suzy
    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
    52,917

    Re: Wanting to string multiple IF(AND) statements together

    Hi, welcome to the forum

    1st some questions...
    is there any relationship between the cities? eg a factor that could be applied?
    is there any relationship between the gas and diesel?? eg again, a factor that could be applied?

    If either of those 2 could have a factor, then I suggest a small table showing the different cities and their rates, and a vlookup()
    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
    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
    52,917

    Re: Wanting to string multiple IF(AND) statements together

    hmm looks like gas = 0.84 * deisel?

  4. #4
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Wanting to string multiple IF(AND) statements together

    Hi,

    I would recommend changing your data array a little bit and use an IF formula call an INDEX MATCH MATCH formula.

    Attached is a version of your file showing how it works.

    The formula is an array formula that needs to be entered with CTRL/SHIFT/ENTER.

    Hope this makes sense and is helpful.

    Cheers
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-27-2017
    Location
    Penticton, BC
    MS-Off Ver
    2016
    Posts
    33

    Re: Wanting to string multiple IF(AND) statements together

    Hi Ford!

    Thanks for the welcome and the suggestions. Our hauling company has different rates per location, per pick up point and per product (Gas is lighter than diesel, the Kamloops rack is closer to our region etc).

    Maybe I'm not understanding what you mean by a factor or relationship? If you look on the spreadsheet there is a tab showing the pricing grid.

    Suz

  6. #6
    Registered User
    Join Date
    03-27-2017
    Location
    Penticton, BC
    MS-Off Ver
    2016
    Posts
    33

    Re: Wanting to string multiple IF(AND) statements together

    Quote Originally Posted by southward View Post
    Hi,

    I would recommend changing your data array a little bit and use an IF formula call an INDEX MATCH MATCH formula.

    Attached is a version of your file showing how it works.

    The formula is an array formula that needs to be entered with CTRL/SHIFT/ENTER.

    Hope this makes sense and is helpful.

    Cheers
    HOLY AMAZING MOLY!!!

    Thank you SO MUCH. I'm going to tear that apart so I can learn it. I had looked at the match function last week but dismissed it for not having enough parameters! Thank you!

    Suz!

  7. #7
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Wanting to string multiple IF(AND) statements together

    You are very welcome. Glad I could be of some assistance.

    Cheers

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

    Re: Wanting to string multiple IF(AND) statements together

    I've changed your rates table around a bit to make it easier to get data from it, and then you can use this formula in cell G2:

    =INDEX(Rates!$B$3:$E$7,MATCH(B2,Rates!$A$3:$A$7,0),MATCH(A2,Rates!$B$1:$E$1,0)+(C2="Gasoline"))

    Copy down to the bottom of your data.

    Hope this helps.

    Pete
    Attached Files Attached Files

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Wanting to string multiple IF(AND) statements together

    Here is a slightly different approach that uses an array formula. It also required your tables to be rearranged. The array formula matches the combination of origin, destination, and fuel type to return the multiplier. (BTW your original formula didn't match your tables.)
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  10. #10
    Registered User
    Join Date
    03-27-2017
    Location
    Penticton, BC
    MS-Off Ver
    2016
    Posts
    33

    Re: Wanting to string multiple IF(AND) statements together

    I'm thoroughly mind blown. Just when you think you're pretty smart, you bump into people like you guys! Thanks so much!

    I have so much to learn and am going to enjoy picking apart all of these formulas! I'm already considering how I can apply this info to some of my other projects!

    Sue

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

    Re: Wanting to string multiple IF(AND) statements together

    Glad to help, Sue - there are often many ways of doing things in Excel.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    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. Wanting to use text string as a numeric value...
    By Isophix in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-10-2016, 01:39 AM
  2. Wanting to run loop for multiple pages
    By VeryConfused in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-04-2015, 10:35 AM
  3. [SOLVED] Wanting to do SUMIF across multiple columns
    By Vx22B012 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-30-2012, 11:50 AM
  4. Replies: 0
    Last Post: 10-10-2012, 10:11 AM
  5. Replies: 6
    Last Post: 04-21-2009, 07:00 PM
  6. Add multiple IF statements to a combined string of cells
    By littledobby in forum Excel General
    Replies: 1
    Last Post: 07-17-2008, 11:31 PM
  7. [SOLVED] I am wanting to set up a template for customer monthly statements
    By nardco.ginger in forum Excel General
    Replies: 0
    Last Post: 06-02-2006, 09:40 AM

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