+ Reply to Thread
Results 1 to 6 of 6

nested if

  1. #1
    Registered User
    Join Date
    09-12-2008
    Location
    Cardiff
    Posts
    7

    nested if

    I am working on a costing model for the manufacturing company I work for. The costing is linked to a large matrix of machine output standards. These standards work for most of our business but have established that 2 scenarios that change the standard outputs. I have therefore added 2 drop down boxes 1) Embossed: YES OR NO 2) Roundel label: YES OR NO

    Putting embossed paper through the machine reduces the output to 75%
    Adding a particular label reduces the output to 66% for all papers even if embossed! All the rest are 100%

    So I have linked all the outputs in the matrix to one cell, currently hardcoded with 100%. I need to replace the 100% with a formula that basically looks up: if the label is YES then 66%, if label NO but embossed YES then 75%, if NO label and NO emboss then 100%. Can anyone help me get this into one nested formula? Many thanks

  2. #2
    Registered User
    Join Date
    08-15-2008
    Location
    Mass
    MS-Off Ver
    2016, 64- Bit
    Posts
    28
    =If(Label=Yes,66,if(Label=No,75,100))

    The word "Label" could be a cell reference, so could the 66 or the 75.
    Last edited by Alexander40; 09-16-2008 at 02:23 PM.

  3. #3
    Registered User
    Join Date
    09-12-2008
    Location
    Cardiff
    Posts
    7

    nested if

    Many thanks but it did not work because of the 2nd part of the formula. If label is YES, 66 is fine. If label is NO it is only 75 if emboss is YES. I have built 4 separate IF(AND statements that produce the following results if true and 0 if not true. Every combination of Y & N's produces 1 true and 3 zero's so I could just add these up to get my final % but would prefer this in one cell if possible. Just dont know how to nest these? Any ideas?

    label emboss result if true
    Y Y 66%
    Y N 66% the label determines both these

    N Y 75%
    N N 100%

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try something like:

    =IF(AND(D1=0,D2=0),"",IF(D1="Yes",66%,IF(D2="No",75%,100%)))

    Where D1 contains Label drop down and D2 contains Emboss drop down
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    09-12-2008
    Location
    Cardiff
    Posts
    7

    nested if results!

    Many thanks for help. First attempt did not work so I broke the formula down to see which part did not work for me. It was the first part: if
    label =0, emboss =0, "". It did not seem to recognise the 0 as the cell it is looking at only has yes or no. I then checked the 2nd part of the the formula from: If label is yes, 66%, if emboss is no etc and this worked !!!!(except I had to change if emboss is yes because it is only the emboss without the label that is 75%) It did not seem to need the first bit?

    I have tested all combinations of yes' and no's and I get the correct % result.

    I must have been doing something wrong as this is what I tried to do earlier today... I knew I had to start with the label being yes, then everything else was 100% except the emboss at 75% but I must have got it in the wrong order. Thanks again, you're a lifesaver!!

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    You're right.. should've been:

    =IF(AND(D1="",D2=""),"",IF(D1="Yes",66%,IF(D2="No",75%,100%)))

+ 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. Create Nested Directories
    By dgkindy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-01-2008, 10:06 AM
  2. not sure how to apply nested if to my problem
    By schueyisking in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-27-2008, 09:41 AM
  3. Replies: 1
    Last Post: 06-17-2008, 02:20 PM
  4. Working around Excel 97 limit of 7 nested IFs.
    By BoulderBill in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-01-2007, 08:40 AM
  5. Overcoming limit of nested IF functions
    By JonBoy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-27-2006, 01:44 PM

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