IF Statement with no priorities

Hello guys, nice forum!

I'm trying to make a product specification to give to clients. I've already figured how to set all the references on the other sheets, but I have a problem on my main table.
Basically I have a list of all different cheeses with all the chemical specification, ingredient statement, flavor, color, texture.
Since we make shredded blends, I have a column where I can choose the percentage of cheese that goes in that blend, and a formula (SUMPRODUCT) that averages the numbers based on the percentages. The problem is that I can't find a formula that does the same job with text.
I tried the IF statement in my Ingredient Statement, but this formula gives "priority" to first true value that it finds.
I'm not sure if I'm clear so I attach a draft of my table, hoping someone can help me find the correct formula.
I filled with yellow the cell where I'm using the (wrong) IF statement.

Thanks guys!

Hi Kraut,

Could you explain a little about what you need to appear in the yellow cell?
You are up to speed with the logic behind the process but we are not so perhaps a little more information will help provide you with a solution.

BSB.

Hi guys,

I'm sorry. Basically what I need is the Blend row, to generate numbers and letters based in the percentage that you select in the left "Percentage" grid.
Example: if you guys type 50% Monterey Jack and 50% Parmesan the Min Moisture on the blend is going to change to 35%, because the Min for MJ is 40% and the Min for Parmesan is 30%. And the way it is, it already works! What doesn't work is the text: I have very little knowledge of formulas so what I need is if I type 50% Smoked Provo and 50% Muenster, on the Blend Ingredient Statement should appear ALL the ingredients for those 2 cheeses:
Pasteurized Milk, Cheese Culture, Salt, Enzymes, Liquid Smoke & Annatto
For the Flavor I should have MILD (cause of the muenster), Sharp and piquant (cause of the smoke provo)

Thanks for checking, hope I'm making some sense!

Hi kraut

Welcome to the forum

My solution is a bit "cludgey" - but it works . Note the use of the helper column AQ.

Regards
Alastair

Wow that is so smart!! Thank you Alastair, you saved me a lot of time!!!

I think the best solution to your problem would be to set up a solver model. Solver an Excel add-in is used among other things for blend optimazation.

In such a model you could specify min and max % of the different components and also add the specificatio i.e. min and max moisture for the finised blend as well as min and max for fat, salt and pH.

One should also add a column for component price to ensure that the finished product has the lowest price.

I could set up a model for that tomorrow is this is of interest.

Alf

Please do!!! I'm having problems to report your formulas into the flavors and textures!!

Thanks!

Having reread your post I'm not so sure anymore that solver is the right tool for you as you seem to work with fixed blends of components and your main problem is to get an automatic update of the finished blend components names.

Still I've set up a solver model that you can test. You must have solver installed on your computer if it is you find it under the "Data" tab in the upper right hand corner. Click on solver and then click "Solve" as this file has a setup solver model.

If not installed see instruction http://office.microsoft.com/en-us/ex...010021570.aspx

If you find solver of use then the automatic update of finished blend component names could be included.

Alf

Hey Alf,

Thanks for the model!! My next question then is: is there a way that I can have Solver to round numbers (I already tried the formulas but couldn't find a way to get what I need) in order to give me a lower number if is under Min Moisture, and a higher number if is a Max Moisture.
Example of Min in a 25% blend: 42, 35, 40 and 30. The average of this numbers is 36.75, but I can't give that number to customer! I want excel to give me 36.5 if is under Min Moisture, or 37.0 if is under Max Moisture.
I tried this formula: =MROUND(ROUNDDOWN(SUMPRODUCT($E$4:$F$14,H4:I14),3),0.5%)
But if the number to round is 46.88, the rounddown round it to 46.80 and the mround round it to 47. While what I need is a 46.50

Any suggestion?

Solver will not round number but you could present the solver result in another part of the sheet and use a formula like this

Please Login or Register  to view this content.

where the value wish to change is in C3

Alf

Hi Alf,

Thanks for the quick response. I didn't know that formula, and also if everything seems logical, I still have a wrong result. May be for the percentage format?

May be for the percentage format

How right you are! I should have thought of this since the INT function strips away the decimal part from a number leaving only the integer!

And since 46.88% as a number is 0.4688 then my proposed solution will not work. I've set up a workaround this problem. A bit clumsy but will have to for the moment. Will see if I can find a better solution but this will have to untill then

Alf

Thanks a lot Alf!!