+ Reply to Thread
Results 1 to 10 of 10

Using IF & AND statements

  1. 03-22-2018, 02:01 PM #1
    Boooya
    Boooya is offline
    Registered User
    Join Date
    03-22-2018
    Location
    WI
    MS-Off Ver
    2016
    Posts
    4

    Using IF & AND statements

    What I am trying to do is have cell H111 on Sheet 'Calc Addl M (samples)' to look to Sheet 'Quote' B24 and if B24 = "40" and B23="8" then enter cost from X5 on 'fedex chart'

    What this is doing is looking at the weight of the piece (either 40lb or 60/70lb, then the page count (8,12,16,24,32) to detemine what the cost of shipping is on Sheet called 'fedex chart'.
    I currently have IF statements to get cost based on page count only, but know I also need weight of piece as the 40lb paper is cheaper to mail then the 60 or 70lb paper.

    Here is my current formula:
    =IF(Quote!B23=8,'fedex chart'!X5,IF(Quote!B23=12,'fedex chart'!X6,IF(Quote!B23=16,'fedex chart'!X6,IF(Quote!B23=20,'fedex chart'!X7,IF(Quote!B23=24,'fedex chart'!X7,'fedex chart'!X8)))))

    I think i need to add an AND statement, but cant seem to get it to work. Any help is greatly appreciated.
    Thanks
    Reply With QuoteRegister To Reply
  2. 03-22-2018, 02:18 PM #2
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Using IF & AND statements

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to Quick Post button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  3. 03-22-2018, 02:46 PM #3
    Boooya
    Boooya is offline
    Registered User
    Join Date
    03-22-2018
    Location
    WI
    MS-Off Ver
    2016
    Posts
    4

    Re: Using IF & AND statements

    File attached. I hope..
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 03-22-2018, 03:31 PM #4
    finalazy
    finalazy is offline
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    192

    Re: Using IF & AND statements

    try this..

    =IF(OR(Quote!B24=60,Quote!B24=70),INDEX('fedex chart'!X5:X8,MATCH(Quote!B23&"pg",'fedex chart'!W5:W8,0))*1.1,IF(Quote!B24=40,INDEX('fedex chart'!AA5:AA8,MATCH(Quote!B23&"pg",'fedex chart'!Z5:Z8,0))*1.1,""))
    Reply With QuoteRegister To Reply
  5. 03-22-2018, 03:42 PM #5
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Using IF & AND statements

    Added Named ranges:
    Pounds40 ='fedex chart'!$Z$5:$AA$8
    Pounds60 ='fedex chart'!$W$5:$X$8
    Pounds70 ='fedex chart'!$W$5:$X$8

    Delete the " pg" characters in fedex chart W5:W8 & z5:Z8 tables and use custom number format: 0 " pg"
    fedex chart, H111

    edit: corrected formula
    =INDEX(INDIRECT("Pounds"&Quote!$B$24),MATCH(Quote!$B$23,INDEX(INDIRECT("pounds" &Quote!$B$24),,1),0),2)*1.1

    fedex chart, H112
    =IF(E112=0,0,INDEX(INDIRECT("Pounds"&Quote!$B$24),MATCH(Quote!$B$23,INDEX(INDIRECT("pounds" &Quote!$B$24),,1),0),2)*1.1)
    Attached Files Attached Files
    Last edited by protonLeah; 03-22-2018 at 11:01 PM.
    Reply With QuoteRegister To Reply
  6. 03-22-2018, 03:51 PM #6
    finalazy
    finalazy is offline
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    192

    Re: Using IF & AND statements

    realize you have pages like 12 or 20 which is not in the fedex chart sheet..

    more tedious way you can use this.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 03-23-2018, 09:56 PM #7
    Boooya
    Boooya is offline
    Registered User
    Join Date
    03-22-2018
    Location
    WI
    MS-Off Ver
    2016
    Posts
    4

    Re: Using IF & AND statements

    I think I really needed to simplify this so I can understand it better. Would you mind showing me the formula now in sheet 'fedex_cost' in cell K4. This way we can reference B8 and B9 of the same sheet. Baby steps for me...
    Really appreciate all youe help. I really like learning about Index and Match functions.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-24-2018, 12:05 AM #8
    FDibbins
    FDibbins is offline
    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,938

    Re: Using IF & AND statements

    Have not (yet) viewed your file, but maybe consider making a small matrix with 1 set of values down 1 side, and the other across the top - and the "answers" inside the table. You could then use INDEX/MATCH to find what you want. This will also give the flexibility to change the table as needed
    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
    Reply With QuoteRegister To Reply
  9. 03-24-2018, 01:13 AM #9
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Using IF & AND statements

    For the new workbook, the named range is:
    PaperWeightTable, refers to =fedex_cost!$E$5:$H$11

    In K4:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 03-26-2018, 02:21 PM #10
    Boooya
    Boooya is offline
    Registered User
    Join Date
    03-22-2018
    Location
    WI
    MS-Off Ver
    2016
    Posts
    4

    Re: Using IF & AND statements

    THANK YOU! this works perfectly!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Nested if statements containing multiple and statements
    By John M. in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-22-2014, 11:48 PM
  2. Formula with multiple IF statements and IF AND statements
    By lottidotti in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-14-2013, 08:03 AM
  3. Reading If statements and formulating values from if statements
    By crnam in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-16-2013, 05:20 AM
  4. questions with if statements and nested if statements
    By Pat Excel in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-03-2013, 01:41 PM
  5. Nested If statements / logic statements
    By Brainless_09 in forum Excel General
    Replies: 3
    Last Post: 06-17-2009, 02:59 PM
  6. Nested IF statements or CHOOSE statements needed for multiple conditions formula
    By salmansaif in forum Excel General
    Replies: 12
    Last Post: 05-15-2009, 08:38 AM
  7. [SOLVED] operator statements, shorting when reusing one of the statements?
    By KR in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-04-2005, 02:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1