Cannot compute a formula to combine 50% Sample and 20% Sample to make it 100%

Hi guys,

I've encountered another problem. I don't even know if this is possible but anyway, I'll try my best to explain the situation.

Mix Proportion (%) 100% = 50% + 20% + 30%

Sieve Size Final Mix Product Sample A Sample B Sample C
37.5mm 100% passing 100% 100% 100%
19mm 94.7% passing 92% 100% 91%
9.5mm 74.9% passing 62% 100% 83%

Liquid Limit: 20.6 22 18 0
Plasticity Index: 3.0 3 3 0
Linear Shrinkage: 3.7 5 1.2 0

On my spreadsheet, I've got 50% of Sample A + 20% Sample B + 30% Sample C to make up a final mix product of 100%.

The Liquid Limit, Plasticity Index and Linear Shrinkage on the Final Mix Product is calculated by:

e.g. Linear Shrinkage of Final Mix Product = Linear Shrinkage of Sample A multiplied by 50% of Sample A and then divide the result by 100 PLUS Linear Shrinkage of Sample B multiplied by 20% of Sample B.

*Note the values on the above example are just random values I entered so they don't necessarily add up correctly.

The Excel allows the user to add up to 5 Samples to make up the final mix product. As you can see for Sample C, the values are zero. I want to ignore this during the calculation. Is there a way where Excel can ignore the 30% Sample C and only use the 50% and 20% Sample and make those two Samples equivalent to 100% and calculate the Liquid Limit etc.

Thanks so much guys. Your help will be much appreciated.

Hi guys,

I've encountered another problem. I don't even know if this is possible but anyway, I'll try my best to explain the situation.

Mix Proportion (%) 100% = 50% + 20% + 30%

Sieve Size Final Mix Product Sample A Sample B Sample C
37.5mm 100% passing 100% 100% 100%
19mm 94.7% passing 92% 100% 91%
9.5mm 74.9% passing 62% 100% 83%

Liquid Limit: 20.6 22 18 0
Plasticity Index: 3.0 3 3 0
Linear Shrinkage: 3.7 5 1.2 0

On my spreadsheet, I've got 50% of Sample A + 20% Sample B + 30% Sample C to make up a final mix product of 100%.

The Liquid Limit, Plasticity Index and Linear Shrinkage on the Final Mix Product is calculated by:

e.g. Linear Shrinkage of Final Mix Product = Linear Shrinkage of Sample A multiplied by 50% of Sample A and then divide the result by 100 PLUS Linear Shrinkage of Sample B multiplied by 20% of Sample B.

*Note the values on the above example are just random values I entered so they don't necessarily add up correctly.

The Excel allows the user to add up to 5 Samples to make up the final mix product. As you can see for Sample C, the values are zero. I want to ignore this during the calculation. Is there a way where Excel can ignore the 30% Sample C and only use the 50% and 20% Sample and make those two Samples equivalent to 100% and calculate the Liquid Limit etc.

Thanks so much guys. Your help will be much appreciated.

I often find that I must understand the math equations before I can work out the Excel programming.

I expect that most of this is relatively simple algebra, but it is not clear to me how it should work out.

Are the mix proportions given, or do they need to be computed from some other inputs? This:

I've got 50% of Sample A + 20% Sample B + 30% Sample C to make up a final mix product of 100%.

sounds like basic addtion (a SUM() function or a series of + operations), assuming the proportions are given. If the proportions need to be computed, what data will they be computed from?

Linear Shrinkage of Final Mix Product = Linear Shrinkage of Sample A multiplied by 50% of Sample A and then divide the result by 100 PLUS Linear Shrinkage of Sample B multiplied by 20% of Sample B.

This sounds like a basic =SUMPRODUCT() function (value(1)*proportion(1)+value(2)*proportion(2)...), though I don't know where the divide by 100 comes from. Sample C does not impact linear shrinkage?

Anyway, if you describe the math equations, I'm sure we can help you program those equations into Excel.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Thanks for the reply. Sorry for my explanation. I know it's a bit vague to understand. I have uploaded my excel spreadsheet. Hope this helps you understand my problem a little better.

To answer your question, the mix proportion varies depending on the user.

Sample C does not impact the Linear Shrinkage etc calculation, but the mix proportion does. I want Excel to somehow neglect the 30% Sample C and make the 50% Sample A and 20% Sample B the 'new' 100% of the final mix design product.
If it works, there will now be 71.4% of Sample A and 28.64% of Sample B (if my math calculation is correct?). This will then give the correct answers to working out the Linear Shrinkage etc for the final mix design.

Hope this makes more sense.

I'm not sure exactly what you need help with.

It sounds like you need a simple "normalization" calculation (if you did it correctly).
50/(50+20)=71.4% and 20/(50+20)=28.6% is that correct? I would usually enter that as =G5/SUM($G5:$H5) Note the use of absolute references to make that easy to copy. As simple as that seems, is there more to getting the new normalization than that?

As I indicated before, once you have the proportions normalized the way you want, the shrinkage (and I assume the others) are simply =sumproduct() functions after that. =SUMPRODUCT(range where you calculate the normalized proportions,G18:H18).

I should note that I prefer to have my percentages/ratios/proportions as fractions between 0 and 1 and not multiply by 100 like your values in G5:I5. As long as you keep track of that factor of 100 where needed, it doesn't matter whether you have it there or not. You just need to pay attention to it as needed.

To make this more flexible, simply replace the SUM() function in my proposed normalization function with a SUMIF() function. Something like =G5/SUMIF($G18:$K$18,">0",$G5:$K5) copied across. Your Linear Shrinkage should still be a simple SUMPRODUCT() of this helper row with row 18, the row containing the linear shrinkage values.

A little algebra (mathophobes beware) might also help us here. the final formula is:
total=conditional sum of a through e
overall linear shrinkage = a/total*ls(a)+b/total*ls(b)+...+e/total*ls(e). Factor out 1/total
overall linear shrinkage = 1/total*(a*ls(a)+b*ls(b)+...+e*ls(e)) Which should be a simple =SUMPRODUCT(G5:K5,G18:K18)/conditional sum from above

I don't think I mentioned putting the normalization function in F5. F5 does not seem like a convenient choice, since you cannot copy the formula to the right. I would probably put this formula into a row below the table (G21, maybe).

Help file for the SUMPRODUCT() function: https://support.office.com/en-us/art...c-4d2145a2fd2e

If I have understood correctly, you describe the overall linear shrinkage as "proportion(a)*linshrink(a)+proportion(b)*linshrink(b)+...+proportion(e)*linshrink(e)" which is the sum of a series of products. Am I understanding this correctly? Assuming I am, you can certainly, if desired, write out the function like that (G21*G18+H21*H18+...), but I find that the SUMPRODUCT() function makes these formulas easier to enter.

I cannot see anything syntactically wrong with the programming aspects. Are you certain the math/logic is what you are expecting?

Where you have a non-zero value for the linear shrinkage of sample C, I was expecting that you would include sample C in the normalization as well as the linear shrinkage, but this spreadsheet seems to ignore samples C, D, and E no matter what is entered for their linear shrinkage. I also note that the normalization does not add up to 100%, because it is not including samples C, D, and E. If this is correct, then there were aspects of this that I was not understanding from the beginning. Which is fine, as long as you understand it well enough to know that it is correct. Since I obviously did not understand, then I am probably not the best to double check your calculations, because I am not sure what the correct calculation should be. If it looks right to you, I will have to take your word for it.

I do note that your use of the SUMPRODUCT() function is completely unnecessary the way you have done it. You are essentially saying =SUMPRODUCT(A*B), which is the exact same thing without the SUMPRODUCT() function. If you are going to formulate the formulas this way, then I would drop the SUMPRODUCT() function completely.

Hey I think it works!!!! Do you think this is correct? Please ignore the previous spreadsheet I sent you.

A sample will either have Liquid Limit, Plasticty Index, Linear Shrinkage ALL have zero values OR a sample will have Liquid Limit, Plasticty Index, Linear Shrinkage ALL have a positive value other than zero.

Thanks MrShorty!