+ Reply to Thread
Results 1 to 12 of 12

Extrapolation formula?

  1. #1
    Forum Contributor
    Join Date
    02-08-2006
    Location
    UK
    MS-Off Ver
    2013
    Posts
    204

    Exclamation 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
    Attached Files Attached Files
    Last edited by wonderdunder; 03-23-2011 at 09:32 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Extrapolation formula?

    Here, try this:


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

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    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. #4
    Forum Contributor
    Join Date
    02-08-2006
    Location
    UK
    MS-Off Ver
    2013
    Posts
    204

    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. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    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. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    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
    Last edited by zbor; 03-23-2011 at 08:01 AM.

  7. #7
    Forum Contributor
    Join Date
    02-08-2006
    Location
    UK
    MS-Off Ver
    2013
    Posts
    204

    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. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    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
    Attached Files Attached Files
    Last edited by zbor; 03-23-2011 at 09:01 AM.

  9. #9
    Forum Contributor
    Join Date
    02-08-2006
    Location
    UK
    MS-Off Ver
    2013
    Posts
    204

    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. #10
    Forum Contributor
    Join Date
    02-08-2006
    Location
    UK
    MS-Off Ver
    2013
    Posts
    204

    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. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    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.
    Last edited by zbor; 03-25-2011 at 04:58 AM.

  12. #12
    Forum Contributor
    Join Date
    02-08-2006
    Location
    UK
    MS-Off Ver
    2013
    Posts
    204

    Re: Extrapolation formula?

    Quote Originally Posted by zbor View Post
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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