Combining functions

young engineer

Board Regular
Joined
Mar 3, 2009
Messages
100
<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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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

Board Regular
Joined
Mar 3, 2009
Messages
100
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,801
Messages
5,638,437
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top