# Extrapolation formula?

1. ## Extrapolation formula?

Hello - I desperately need help urgently with this one.

I have a list of values (Column A) with a corresponding reference value (Column B).

In E1 the user enters any value. E2 is then to calculate the extrapolated corresponding value from column B.

for example.

User enters 0.6 in E1.
E2 is supposed to work out a reference value based on what is available in columns A & B.
So the nearest values is 0.5 and 1. with corresponding reference values of .08 and 0.1
E2 therefore needs to be a value somewhere between .08 and 0.1

how is that calculated? the majority of it is linear but not all of it.

Thanks for any help

2. ## Re: Extrapolation formula?

Here, try this:

=TREND(\$B\$2:\$B\$31, \$A\$2:\$A\$31, E1)

3. ## Re: Extrapolation formula?

If that's not good for you you can divide your data into several partial linear areas and then place your data.

4. ## Re: Extrapolation formula?

Many thanks for responding - I've spent ages trying to get that trend function to work with no joy!

It works for higher values (above 100 which is almost linear), but for lower values it doesn't at all - in fact it seems to go in the opposite direction of what it's supposed to do!

I'm not sure what you mean by your second post. Unfortunately, there are 15 sets of this type of data to go through and splitting anything up woul not really work. I don't mind using a couple of different formulas using an if statement (so if the value is above 100 then use trend, esle use something else), but I don't know what that something else would be. Ideally one formula to fit all would be best.

5. ## Re: Extrapolation formula?

Not necessarily 15 ranges.. as I can see there are only 3:

less then 0,005 (always 0,02)
then less then 50 and rest:

=MAX(0,02,IF(E1<=50,TREND(\$B\$17:\$B\$28,\$A\$17:\$A\$28,E1),TREND(\$B\$2:\$B\$16,\$A\$2:\$A\$16,E1)))

6. ## Re: Extrapolation formula?

OK, maybe one more:

=MAX(0,02;IF(E1<=10;TREND(\$B\$19:\$B\$28;\$A\$19:\$A\$28;E1);IF(E1<100;TREND(B16:B18;A16:A18;E1);TREND(\$B\$2:\$B\$20;\$A\$2:\$A\$20;E1))))

Now, if you need more precise probably some INDIRECT or OFFSET approach would neet to fix the ranges in single formula...

Give some feedback so we can continue

7. ## Re: Extrapolation formula?

Hi Again, that's only giving a value of 2 for the most part for figures below 400.

I've tried to restrict the range (and data entered into e1), to anything below about 100, but it tends to give the same result - i.e, almost as if it's going the wrong way.

With the example in my post, the return value should be about .085, but instead it returns .037 which is was off the mark.

I think another way around this might be to look at the values either side of the figure entered in E1, calculate the distance between them and then apply the same to the corresponding values?

E.g, 7 is entered by the user which is just under half way between 5 and 10 (0.16 and 0.2 being the corrsponding value) - E2 could therefore be the same distance between 0.16 and 0.2. How would that be done?

Oh I wish I paid attention in maths lessons!

8. ## Re: Extrapolation formula?

Here, found it...

With a % difference from true result:

=MAX(0.02,IF(E1<100,0.0984*POWER(E1,0.2999),TREND(\$B\$2:\$B\$19,\$A\$2:\$A\$19,E1)))

Biggest difference is 6%.. If that's mutch it can be repaired with new criteria from 20 to 200

9. ## Re: Extrapolation formula?

Brilliant - thank you so much!

It seems to work - there's a damn load of test data I've got to get through but so far it's looking good.

Thanks again.

10. ## Re: Extrapolation formula?

Just one more question - how did you arrive at the formula for when the value is below 100?
I know that the value of gravity is used (0.0984) - but what the figure 0.2999?

There are several other columns of data that I need to use this on but I think the second figure needs to be different but don't know what!

11. ## Re: Extrapolation formula?

I'm not a math wunderkid but I'll explain what I did:

First create scatter graph (select two ranges of data (A and B) Insert-> Graph (Scatter, line))

Now, if you are lucky then your data will fit one curve (for example all linear from first to last).

If not you will have 2 or more curves that you need to check.

in your example there's a 2 of them.

1st is linear for data from 5000000 to 100
there's a breaking point for
2nd range from 0,005 to 100 which is POWER
3 range is a constant (0,02) for all numbers less then 0,005

To get this equation of 2nd range (or in other cases for all ranges) you need to right click on your data -> Add Trendline and then you'll get a window as in the picture below:
Untitled.jpg

Now you need to check all Trens/Regression Types to see which one fit you best.

if it fits like in this case with POWER curve then check box down there Display Equation on the chart which will show you equation (see right aproximation and equation) and then you need to write it in excel (consider that 5x is 5*\$F\$1 in excel)

If it doesn't fit you need to take several steps.

Note: To check is your data linear you need to take 3 (three) points. Or more, but not less.
If they are linear you can extend it to 4, 5 or more points until it become unlinear. Take unlinear data and try to aproximate it with another curve.

I hope this helps.

12. ## Re: Extrapolation formula?

Originally Posted by zbor
I'm not a math wunderkid.....
Yeah right! Thanks again very much - it's really helped

They are all powers and follow a rather interesting series - well interesting in my line of work anyway!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1