nested if

[molly147]

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

[Alexander40]

=If(Label=Yes,66,if(Label=No,75,100))

The word "Label" could be a cell reference, so could the 66 or the 75.

[molly147]

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%

[NBVC]

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

[molly147]

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!!

[NBVC]

You're right.. should've been:

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