i want get formula in this chart ( y is log scale)

Question

i have a big problem...

first.. sorry for my bad english...and go to link

http://www.fhwa.dot.gov/pavement/recycling/98042/07.cfm

in document..

Step 4 - Select grade of new asphalt binder

On figure 7-4, Point A is the viscosity of the aged asphalt binder at 46,000 >poises (4.6 × 104). Point B is located from a target viscosity of 2,000 poises >(2.0 × 103) and R = 69. The projected line from Point A through Point B to >Point C indicated that the viscosity of the new asphalt binder is 7.0 × 102 (700). Figure 7-4. Asphalt viscosity blending chart (design example 1).

http://www.fhwa.dot.gov/pavement/recycling/98042/images/f089.gif

there are three points in chart. y is log scale. how to get y ? (x=100, y=?) using MS excel ... (no need chart, i need only formula)

i tried. (0, 46000) (69, 2000) (100, y) -> to log scale (0, 4.662757832) (69, 3.301029996)

y =ax + b -> a = (3.301029996 - 4.662757832)/(69-0) = -0.019735186 b = y - ax = 3.301029996 - (-0.019735186) * 69 = 4.662757832 ∴ y = -0.019735186 * 100 + 4.662757832 = 2.689239229 10^2.689239229 = 488.921605 (no 700)

what is my mistake??

Answer 1

The data mention in the article $(0,46000)$, $(69,2000)$ and $(100,700)$ do not lie on a straight line when drawn on a logarithmic graph. To see this calculate the gradient between each pair of points and notice they are not equal.

$$\frac{\log_{10}46000-\log_{10}2000}{0-69}=-0.0197352$$

$$\frac{\log_{10}46000-\log_{10}700}{0-100}=-0.0181766$$

$$\frac{\log_{10}2000-\log{10}700}{69-100}=-0.0147075$$

So in conclusion you haven't done anything wrong and they shouldn't have gotten $700$. Your answer of $489$ fits the regression.

Answer 2

If you consider the the data points $(0,46000)$, $(69,2000)$, $(100,700)$ and perform a linear regression $$\log_{10}(y)=a+b \,x$$ you should get $$\log_{10}(y)=4.64155 -0.0184365\, x$$ Using this model, the predicted values along the curve would be $43808,2341,628$.

This seems to be the best compromise in the least square sense.

Edit

If you want a perfect fit (perfect since you only use three data points), you could use $$\log_{10}(y)=4.66276-0.0232043 x+0.000050277 x^2$$ For $x=40$, this would lead to $y=6532$ which seems to be quite good looking at the plot you refer to.