Combining functions

[young engineer]

<TABLE style="WIDTH: 581pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=774 border=0><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=3 width=64><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=6 width=64><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><TBODY><TR style="HEIGHT: 46.5pt" height=62><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; HEIGHT: 46.5pt; BACKGROUND-COLOR: transparent" width=64 height=62>Brix Read UnDiluted</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=64>Brix Read Diluted solution</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=64>Angle Diluted solution</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=64>Brixcorr</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 53pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=70>Brixxcorr [F &G]</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=64>Brix Corr 2 [g/cm3]</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=64>SG</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=64>G[g] in 100cm3 Diluted solution</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=64>F[g] in 100 cm3 Diluted solution</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=64>G[g] in 25cm3</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=64>F[g] in 25cm3</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 1pt solid; BACKGROUND-COLOR: transparent" width=64>Fructose Purity [%]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>0.1</TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #dbe5f1">0.02</TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #dbe5f1">0.01</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">0.0</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">0.0</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">0.02</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1.0</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">0.0</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">0.0</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">0.0</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">0.0</TD><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> 19.23 </TD></TR></TBODY></TABLE>


Brixcorr : =+brixcor(E2)
Brixcorr[f&G] : =+G2+E2
Brix corr2: =H2*J2
Sg: =+SG(E2)
G[g]in 100: =I2-L2
F[g]in 100: =fruc(I2,F2)
G[g] in 25: =K2*5*15/100
F[g]in 25: =L2*5*15/100
Fructose Purity: =Frucpurity(fruc(I2,F2),I2)

Function brixcor(Brix As Double)
A = 0.006222
B = 0.00023725
C = -0.0000018165
D = 0.000000018906
E = 0.00002328
brixcor = A * Brix + B * Brix ^ 2 + C * Brix ^ 3 + D * Brix ^ 4 + E * Brix ^ 2
End Function

Function SG(dil)
SG = 1 + [(dil ^ 2 + 200 * dil) / 54000]
End Function

Function fruc(bxcorr2, angle1)
A = 52.5
B = 143.8
C = 50
fruc = ((A * bxcorr2) / B) - ((C * angle1) / B)
End Function

Function Frucpurity(fruc, bxcorr2)
Frucpurity = (fruc / bxcorr2) * 100
End Function


Hi All
For the above information I simply have to plug in the the 3 shadded entries to get my fructose purity in the last column. There are however sub-calculations from brixcorr to F[g]in25cm3 leading to the fructose purity. I would like to incorporate all the sub-calculations in my 3 data entries to get the fructose purity. Ultimately i would like to remove all the sub-calculations.
I do not want to hide these columns, i need to have them removed.

I have attached all the functions and cell calculations for this problem.

Any help will be much appreciated

Thanks

 

[jbeaucaire]

The trick to creating a megaformula is to work backwards. Start with your final formula, it has two references to the cell I2. Go to I2 and copy the entire formula from that cell except the starting = sign, then return to that final formula and replace both of the I2 references with the complete formula.

Check your other remaining formulas for references to I2 and do the same. When all I2 references have been removed/replaced with formulas, you can clear cell I2 because its no longer needed.

Now return to your final formula and do the same thing for the F2 reference. Again, with whichever other formulas remain, spot their F2 references and replace them with the formula from F2, then clear F2 as unneeded.

Now return to your last formula. At this point you've introduced some new variables in the formulas you pasted in. Go through the same process.

You continue to replace cell references with the formulas IN those cells until all the cell references in the final formula have been replaced by formulas and the ONLY remaining cell references are to the 3 input cells. By now you have managed to also delete all the intra-vening formula cells and you're done.

 

[young engineer]

The columns are labelled D, E, F,G,H,I,J,K,L,M,N,O

 

[jbeaucaire]

Is there a question about the instructions given in post #2? Follow those steps and you'll be done in no time.

The good thing about this technique is that as you replace each reference with a formula snippet, the value in the final cell should remain the same. It's like an instant verification that the change you made was correct.

So, in your sample above the final result appears to be 19.23%...as you make each change, it should stay 19.23%. Good luck, it's easy after you make the first couple of substitutions and it starts to make sense.

 

[young engineer]

Is there a question about the instructions given in post #2? Follow those steps and you'll be done in no time.

The good thing about this technique is that as you replace each reference with a formula snippet, the value in the final cell should remain the same. It's like an instant verification that the change you made was correct.

So, in your sample above the final result appears to be 19.23%...as you make each change, it should stay 19.23%. Good luck, it's easy after you make the first couple of substitutions and it starts to make sense.

Thanka a Lot
Tried it out, and it worked perfectly

 

[jbeaucaire]

Good job. And that's the technique I use for solving most issues here on the forum that end up with some massive formula. It's SO much easier to simply work out the pieces of logic in separate logical cells, then use the megaformula method to merge it all into one cell.