How does one enter in two variables, both of which have to corrected, to get an answer?

[timcumming11291]

Hello,
I am attempting to speed up the process by which we calculate weight percent of sulphuric acid based off specific gravity and temperature.
The issue I am having is, the values given in the charts we have are not exact, therefore we have a hand-written equation that is tried-and-true method of determining the actual weight percent. However, it takes valuable time away from productivity, and since we have Excel, we might as well get our money's worth.
I will attempt to re-create the workbook I have now, but the HTML maker is down at the moment.

Below is just an excerpt, the workbook itself is roughly 500 rows.
I have placed in the more relevant weight percent area that I am interested in.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Specific Gravity[/TD]
[TD]Wt %[/TD]
[TD]Temp[/TD]
[TD]Correction[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]Corrected[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD]1.3942[/TD]
[TD]49.47[/TD]
[TD]80[/TD]
[TD]0.008[/TD]
[TD]I[/TD]
[TD]SpG[/TD]
[TD]1.3992[/TD]
[TD]1.3842[/TD]
[/TR]
[TR]
[TD]1.3956[/TD]
[TD]49.61[/TD]
[TD]82[/TD]
[TD]0.008[/TD]
[TD]II[/TD]
[TD]Temp[/TD]
[TD]60[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]1.3969[/TD]
[TD]49.75[/TD]
[TD]84[/TD]
[TD]0.009[/TD]
[TD]III[/TD]
[TD]wt %[/TD]
[TD]49.987[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.3983[/TD]
[TD]49.89[/TD]
[TD]86[/TD]
[TD]0.010[/TD]
[TD][/TD]
[TD]Relevant Info[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.3996[/TD]
[TD]50.03[/TD]
[TD]88[/TD]
[TD]0.011[/TD]
[TD]A[/TD]
[TD]SpG Low[/TD]
[TD]1.3983[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.4010[/TD]
[TD]50.17[/TD]
[TD]90[/TD]
[TD]0.012[/TD]
[TD]B[/TD]
[TD]SpG High[/TD]
[TD]1.3996[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.4023[/TD]
[TD]50.31[/TD]
[TD]92[/TD]
[TD]0.012[/TD]
[TD]C[/TD]
[TD]wt % Low[/TD]
[TD]49.89[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.4037[/TD]
[TD]50.45[/TD]
[TD]94[/TD]
[TD]0.013[/TD]
[TD]D[/TD]
[TD]wt % High[/TD]
[TD]50.03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.4050[/TD]
[TD]50.59[/TD]
[TD]96[/TD]
[TD]0.014[/TD]
[TD]Step[/TD]
[TD]Solution[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]I - A[/TD]
[TD]0.009[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]B - A[/TD]
[TD]0.0013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]I - A/B - A[/TD]
[TD]0.692308[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]D -C[/TD]
[TD]0.14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD](3) * (4)[/TD]
[TD]0.0969231[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]C + (5)[/TD]
[TD]49.987[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The equation used for determining weight percent is dependent upon specific gravity and temperature, as stated above.
All cell references will be relevant to the above chart.
Columns A and B are data from a chart on sulphuric acid data when it is at a temperature of 60 F.
Columns C and D are data from a chart on specific gravity correction dependent upon temperature of the sulphuric acid.
Column E is for simplicity of understanding Column F.
Column F is the data as well as the equation for determining wt %.
Column G is the numerical data.
Golumn H is the data obtained from a sample.

To get the value in G2, one must reference cell H2 and H3. Then, using Columns C and D, find the corrected specific gravity.
To get the answer in F16, one must follow steps 1-6, which uses the specific gravity and weight percent surrounding the data in question.

F3 must remain at 60 because the data in Columns A and B reference sulphuric acid at 60 F.

As for the moment, we do all of this by-hand. It is simple enough, but time-consuming.
The goal is simple, be able to put the information in cell H2 and H3, and have the data in F16 populate automatically and correctly.

I have looked into VLookup tables and thought they would work, however, they do not seem to fit my application.

Any help anyone can provide would be absolutely fantastic.

Thanks so much,
Tim

 

[BJungheim]

You should be able to plug

=H2+LOOKUP(H3,$C$2:$D$11)

in for your G2 formula (though your table doesn't go up to 98, I'm assuming it is the +0.015 difference). I'm not sure how you're calculating the Wt% since the exact SpG is not in the table in A:B and it is not a simple ratio of SpG to Wt%. G3 can just be 60, since you'll always be adjusting it to that (unless the % or # change from the 98 drives the Wt%).

 

[timcumming11291]

BJungeim,
I appreciate the Lookup, that helped solve the problem of correcting the SpG to the 60 reference.
And I appologise about the error I made not including the correction for 98 deg F.

The next part of my question, however, remains unanswered.
The calculation for weight percent is lined out in cells E11:G16.
Weight percent calculation
Step 1: You take the corrected specific gravity and subtract from that the SpG directly below that value (Column A).
Step 2: Then subtract the SpG value directly above the corrected SpG from the value directly below.
Step 3: Then divide step 1 by step 2.
Step 4: Then, you take the difference in the weight percents for the SpG values surrounding the corrected SpG.
Step 5: Multiply the value from Step 3 by value in step 4.
Step 6: Add the value from step 5 to the weight percent of the lower SpG (Columns A and B).

The relation between SpG and wt % is not a straight line, otherwise a simple equation would suffice and eliminate the need for tedious scratch work.
Once we get a corrected value for SpG, we will no longer need to worry about temperature.

The SpG and wt % chart (in full) can be found here, if anyone is interested in seeing the source of our reference data.

The exact SpG will very rarely be in the chart we have, which is why we have the tedious calculation we have to do every time we get a sample, hence my attempt to speed up the process.


Since the Lookup calculation worked for correcting the SpG needed, does anyone know of a way to have A, B, C, and D autopopulate with the SpG Low, High, wt % Low, High when entering a value into H2 and H3?

Thanks so much already!

 

[BJungheim]

I apologize, I missed completely that the A-D sections were in the charts.

G6

=VLOOKUP(G2,$A$2:$A$10,1)

G7

=INDEX($A$2:$A$10,MATCH(G6,$A$2:$A$10,0)+1)

G8

=VLOOKUP(G6,$A$2:$B$10,2,0)

G9

=VLOOKUP(G7,$A$2:$B$10,2,0)

Should get you what you need.

 

[timcumming11291]

That was perfect!
Thank you so much.