Need help on an algebra formula in excel, Cant figure it out.

[born2dive00]

Hello all

I am not a mathmatician and have a rudimentary knowledge of excel so when I was asked how to do this for a friend I knew who to turn to you guys are great!

1.08 Is the specific weight/ density of ester gum (ingredient in drinks) and X represents the amount in ml I am needing.


HERE IS THE FORMULA I AM WORKING WITH.
Step 2: Insert the information above into the equation to solve for X.


(11.83mL)(0.838) + (4,778.51mL)(0.894) + (1,922.05mL)(0.910) + (59.14mL)(0.858) + (1,230.11mL)(0.855) + 1.08X
----------------------------------------------------------------------------------------------------------------------------------------------=1.0
11.83mL + 4,778.51mL + 1,922.05mL + 59.14mL + 1,230.11mL + X


Step 3: Solve for X.
9.91mL + 4,271.99mL + 1,749.07mL + 50.74mL + 1,051.74mL + 1.08X
--------------------------------------------------------------------------------------------=1.0
8,001.64mL + X


So I can get values shown below in the NUMERATOR using this formula in one cell
=(11.83*.838)+(4778.51*.894) +(1922.05*.910)+(59.14*.858)+(1230.11*.855) BUT I CANT FIGURE OUT THE 1.08x

7133.45mL + 1.08X
--------------------------------= 1.0
8,001.64mL + X

I can get the values in the DENOMINATOR using this formula in another cell =11.813+4778.51+1922.05+59.14+1230.11 BUT I CANT FIGURE OUT THE X

But here is where I am running into the problems trying to get this into excel and solve


7133.45mL + 1.08X = 8,001.64mL + X

0.08X = 868.19mL

X = 10,852.38mL



The 1.08 is the density of the ester gum, and X is the amount of ester gum I will need.

Can any wise man please put this into a working formula in an excel sheet for me please. I can not figure out how to solve this for X long hand much less in excel

Thank you all in advanced I am pulling my hair out of my head!!!

Sincerely
Brad

I have attached the pdf were the formula came from

[shg]

Can you take a deep breath and explain what you're trying to do?

You have a mixture of various fluids, know the amount and density of each, and want to know ...?

[born2dive00]

Hello SHG

If you look at the attachment you can see exactly the formula that I am trying to build in excel.

Basically I am trying to solve for X to tell me how much ester gum I will need for the amounts stated in the formula. Once I get the example formula working then I can just link the cells. to the data

[born2dive00]

Hello SHG

Thank you for your reply

Basically I am trying to take the algebra formula and solve for X the amount of Ester Gum I will need.

1.08 is the density of the Ester Gum, and X is the amount of Ester gum I will need to make the product.

I have solved the numbers for the numerator, (except for 1.08X)
and I have solved the numbers for the denominator (except for X) and the whole thing =1

It has been over 40 years since I learned algebra and never use it in daily life so I can not figure out how to get part after where it says solve for X Basically everything in RED.

And I can not figure out how to do this in excel

Please you help would be greatly appreciated on this matter.

Sincerely
Brad

[shg]

	A	B	C	D
1	Item	Density	Volume
2	Orange topnote	0.838	11.83
3	Orange essence oil	0.894	4,778.51
4	Cold pressed orange oil	0.910	1,922.05
5	Cold pressed tangerine oil	0.858	59.14
6	Orange drink flavor	0.855	1,230.11
7	Ester Gum	1.080	10,852.34	C7: =(SUM(C2:C6) - SUMPRODUCT(C2:C6, B2:B6)) / (B7 - 1)

I think they did a great job making a simple calculation look complicated.

[joeu2004]

So I can get values shown below in the NUMERATOR using this formula in one cell
=(11.83*.838)+(4778.51*.894)+(1922.05*.910)+(59.14*.858)+(1230.11*.855)
[....]
I can get the values in the DENOMINATOR using this formula in another cell
=11.813+4778.51+1922.05+59.14+1230.11

But here is where I am running into the problems trying to get this into excel and solve
7133.45mL + 1.08X = 8,001.64mL + X
0.08X = 868.19mL
X = 10,852.38mL

You really almost have it.

Suppose the numerator formula (the constant part) is in N1, and the denominator formula (the constant part) is in D1.

But note that you have a typo in your posted formula in D1. The correct formula is:
=11.83+4778.51+1922.05+59.14+1230.11

So algebraically, we want to solve for "x" in the mathematical formula:

(N1 + 1.08*x) / (D1 + x) = 1

N1 + 1.08*x = D1 + x
1.08*x - x = D1 - N1
x * (1.08 - 1) = D1 - N1
x * 0.08 = D1 - N1
x = (D1 - N1) / 0.08

So ostensibly, the Excel formula in X1 would be:

=(D1 - N1) / 0.08

However, as written, X1 will display 10852.34 (10852.335625), not 10852.38, when displayed as Number with 2 decimal places.

The reason is: the article rounds each product to 2 decimal places.

So the equivalent formula in N1 should be:
=ROUND(11.83*0.838,2)+ROUND(4778.51*0.894,2)+ROUND(1922.05*0.91,2)+ROUND(59.14*0.858,2)+ROUND(1230.11*0.855,2)

And the formula in X1 should be:
=ROUND((D1 - N1) / 0.08, 2)

[joeu2004]

shg did a good job of showing you how to generalize the problem set-up and solution.

But if it is important to get a result that matches the article, the formula in C7 should be:

=ROUND((SUM(C2:C6) - SUMPRODUCT(ROUND(C2:C6*B2:B6,2))) / (B7 - 1),2)

formatted as Number with 2 decimal places.

[shg]

But if it is important to get a result that matches the article,

That would be mimicking bad behavior.

[born2dive00]

True SHG but due to the density of the items that change and the types of oils used this would I feel be the most accurate to simply drop in and go.
Either way it works I dont care

YOU GUYS ARE GREAT!!!!!! THANKS ALOT!!

[joeu2004]

That would be mimicking bad behavior.

Yes and no. I was tempted to make a similar comment.

Then I remembered the rules of engineering math: calculations should be rounded to the precision of the least accurate measurements, IIRC.

Again, I would tend to agree with you. But I wanted to explain why the results are different.

Then Brad, not us, can make an informed decision.

[born2dive00]

Hello Joeu

Considering that some of my calculations are out to 15 to 20 decimal places it matters very little if it is rounded to 2 or 3.

For example in your 12 ounce can of coke there would be .001555035 of a ml of orange oil. Try measuring that out on a pipet!!! LOL I have to make my oils mixes for at least 100 gallons to get any where the 1ml marks on oils.

Also the specific gravity in beverage and food use is typically out to 3 or 4 decimal places.

BUT THANK YOU for your in put as I will be putting this into my excel formula bank to use on other projects.

Thank you all You all are CHAMPS!!! cheers!!

[joeu2004]

Considering that some of my calculations are out to 15 to 20 decimal places it matters very little if it is rounded to 2 or 3.
[....]
Also the specific gravity in beverage and food use is typically out to 3 or 4 decimal places.

FYI, food for thought (pun intended!)....

Even though Excel claims accuracy to "15" significant digits (that's just a data entry and formatting limitation), most decimal fractions cannot be represented exactly, due to the internal binary representation. And most of time, any multiplication of decimal fractions and any division "destroys" any accuracy they might have. Even simple subtraction. For example,
IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!).

So some explicit rounding of arithmetic with decimal fractions (and any division) is prudent to do for many calculations. The question is: whether or not to do it; and if so, when?

(I tend to defer rounding to as "late" as possible in the calculations. So I would not have rounded the pairwise products, as the article did. Again, I just wanted to explain why the "normal" Excel result differs from the article.)

The decision is entirely yours to make. But don't delude yourself into thinking that a calculation "out to 15 or 20 decimal places" is necessarily more accurate.

PS.... If you would really like to round to a specific number of significant digits, not decimal places, which is really what engineering math says to do, use the following paradigm (rounding to 3 significant digits, for example):

--TEXT(expression,"0.00E+0")

The double negate converts text to numeric results.

[MrShorty]

FWIW, this chemist was always to taught to perform all intermediate calculations to the full precision of the calculator/computer program, rounding only the final result(s). A thorough, rigorous calculation would include the propagation of error (non-rigorous would include some estimate of the error), and the number of significant digits should be somewhat consistent with the calculated/estimated error.