+ Reply to Thread
Results 1 to 14 of 14

Interpolation

  1. #1
    Registered User
    Join Date
    04-11-2016
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    40

    Interpolation

    Hi there,

    I am working on generating an excel spreadsheet that can undergo interpolation.

    e.g. I have the following data

    Sieve size (mm) Percentage passing (%)

    500 "Blank Cell"
    300 "Blank Cell"
    150 100
    100 "Blank Cell"
    75 87
    63 "Blank Cell"
    53 68
    37.5 51
    26.5 41
    19 36
    16 "Blank Cell"
    13.2 "Blank Cell"
    9.5 "Blank Cell"
    6.7 "Blank Cell"
    4.75 3.5
    2.36 3.3
    0.075 2.9

    The main aim of this excel spreadsheet is to find out what sieve size (mm) it is when the exactly 10% of the materials passes a particular sieve size.

    So in the example above, given the data I received, it is logical to use the values 19, 36 and 4.75, 3.5.

    So mathematically it would be:

    x1 = 36% y1 = 19 mm
    x2 = 10% y2 = x mm
    x3 = 3.5% y3 = 4.75 mm

    Therefore, 10% of the material will pass at 7.6 mm sieve size.

    So far, I have a method/formula (=IF($D8>10,IF($D9<10,1,0),0))) that can interpolate ONLY when the blank cells have a value. Right now, the formula is only taking the values 19, 36 and 16, "Blank Cell" to interpolate which obviously gave me an incorrect answer.

    I want to know if you guys can help me find a formula or a method that will automatically take the values 19, 36 and 4.75, 3.5 and ignore/skip the "Blank Cells".

    Note: I need a method that is adaptable to all situations given the fact that the amount of data I receive varies, e.g. one day I could have 3 "Blank Cells" or 6 "Blank cells". The 17 sieve sizes used are fixed.

    Or, if you guys have a different approach, I'd love to hear that as well.
    I've spent an entire week trying to figure out how to solve this problem.
    Your help will be much appreciated, thanks.
    Last edited by K.Lung; 04-11-2016 at 08:47 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Interpolation

    Is it necessary to have the interpolation and the input data exist in the same table? If I were doing this, I would put the known points in their own table (along with a LINEST() function to compute slope and intercept for each pair). Then I would have a second, larger table, with the all of the desired points, then use a lookup function to return slope and intercept for the desired point, and compute the interpolation.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Interpolation

    Hi there,

    See if the attached workbook gets you moving in the right direction.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Interpolation

    Here is a solution that would give you the 7.6 answer that you expected from the data provided. The formula that completes the value of Y2 is:
    Please Login or Register  to view this content.
    Looking at the formula shows that there is a helper table (and column) involved.
    Here is a spreadsheet that shows how the answer was attained from data in post #1: Solve by Linear Interpolation.xlsx
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    04-11-2016
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    40

    Re: Interpolation

    Thank you so much for your help Greg! I will go back and have a quick look at your excel spreadsheet.

  6. #6
    Registered User
    Join Date
    04-11-2016
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    40

    Re: Interpolation

    Appreciate your help JeteMc!

  7. #7
    Registered User
    Join Date
    04-11-2016
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    40

    Re: Interpolation

    Hi Greg, thanks again for sending your excel spreadsheet to me. It works like magic! I've just got one minor problem. 10% passing at a particular sieve size will not generate an accurate result when I've been given a data like this:

    Sieve (mm) % Passing
    500 -
    300 -
    150 100
    100 -
    75 97
    63 -
    53 74
    37.5 56
    26.5 42
    19 39
    16 -
    13.2 -
    9.5 -
    6.7 -
    4.75 16
    2.36 12
    0.075 6

    If I were to manually calculate the sieve size at which 10% of the material passes, I'd choose the values 19mm, 39% and 4.75mm, 16% to obtain the most accurate answer.

    However, given the formula used, =IF(AND(W9<10,W8>10),W9,0) and =IF(AND(W8>10,W9<10),W8,0), it will only read the 2.36mm, 12% and 0.075mm, 6%. This is because the 39% and 16% are both greater than 10.

    I was just wondering, is there a way to fix this by altering the formula or do I just have to manually change a few things whenever I get a data like that?

    Thanks.

    Regards,
    Kelvin

  8. #8
    Registered User
    Join Date
    04-11-2016
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    40

    Re: Interpolation

    Hi Greg, thanks again for sending your excel spreadsheet to me. It works like magic! I've just got one minor problem. 10% passing at a particular sieve size will not generate an accurate result when I've been given a data like this:

    Sieve (mm) % Passing
    500 -
    300 -
    150 100
    100 -
    75 97
    63 -
    53 74
    37.5 56
    26.5 42
    19 39
    16 -
    13.2 -
    9.5 -
    6.7 -
    4.75 16
    2.36 12
    0.075 6

    If I were to manually calculate the sieve size at which 10% of the material passes, I'd choose the values 19mm, 39% and 4.75mm, 16% to obtain the most accurate answer.

    However, given the formula used, =IF(AND(W9<10,W8>10),W9,0) and =IF(AND(W8>10,W9<10),W8,0), it will only read the 2.36mm, 12% and 0.075mm, 6%. This is because the 39% and 16% are both greater than 10.

    I was just wondering, is there a way to fix this by altering the formula or do I just have to manually change a few things whenever I get a data like that?

    Thanks.

    Regards,
    Kelvin

  9. #9
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Interpolation

    Hi again Kevin,

    Many thanks for your feedback and also for the Reputation increase - much appreciated

    I'm sending this from my phone as I'm away from my computer and Excel for two weeks, so I can't really make any meaningful suggestion at this stage.

    I'll check this thread when I get home to see what the situation is.

    Best regards,

    Greg M

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Interpolation

    Until Greg can look at this again, I will venture this question:
    If I were to manually calculate the sieve size at which 10% of the material passes, I'd choose the values 19mm, 39% and 4.75mm, 16% to obtain the most accurate answer.
    What is the logic behind this choice? Why, for the 10% point, do you use the 39%-16% range for the "interpolation", and not use the 12%-6% range at the bottom of this table? The latter choice is what I normally think of as "interpolation", where the former choice is more of an "extrapolation" (since 10% is not between 39 and 16%). In order to modify Greg's spreadsheet, I think we will first need to understand the logic behind this choice. Once the logic is properly understood, then we should be able to adapt the formulas in the "helper columns" to reflect the additions to the logic behind the choice.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Interpolation

    I recollect that particle sizes in aggregates tend to follow a log-normal distribution. 'Tis true, you could regress the sieve amounts to that distribution using Solver, and then interpolate using the inverse function. For example,


    B
    C
    D
    E
    F
    2
    u
    3.44884
    3
    s
    0.957731
    4
    RMS Err
    0.0421
    D4: {=SQRT(AVERAGE((IF(C7:C23 <> "", (D7:D23 - C7:C23)^2))))}
    5
    6
    Sieve
    Fraction
    LN CDF
    7
    0.075
    2.9%
    0.0000
    D7: =LOGNORMDIST(B7, u, s)
    8
    2.36
    3.3%
    0.0034
    9
    4.75
    3.5%
    0.0242
    10
    6.7
    0.0532
    11
    9.5
    0.1056
    12
    13.2
    0.1822
    13
    16.0
    0.2401
    14
    19.0
    36.0%
    0.2992
    15
    26.5
    41.0%
    0.4289
    16
    37.5
    51.0%
    0.5727
    17
    53
    68.0%
    0.7069
    18
    63
    0.7658
    19
    75
    87.0%
    0.8178
    20
    100
    0.8864
    21
    150
    100.0%
    0.9485
    22
    300
    0.9907
    23
    500
    0.9981
    24
    25
    Fraction
    Sieve
    26
    10%
    9.22
    D26: =LOGNORM.INV(C26, u, s)
    27
    15%
    11.66
    28
    20%
    14.05
    29
    25%
    16.49
    30
    30%
    19.04
    31
    35%
    21.75
    32
    40%
    24.69
    33
    45%
    27.90
    34
    50%
    31.46
    35
    55%
    35.49
    36
    60%
    40.10
    37
    65%
    45.51
    38
    70%
    51.99
    39
    75%
    60.03
    40
    80%
    70.45
    41
    85%
    84.90
    42
    90%
    107.36
    43
    95%
    152.04


    Use Solver to minimize D4 by changing u and s.
    Entia non sunt multiplicanda sine necessitate

  12. #12
    Registered User
    Join Date
    04-11-2016
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    40

    Re: Interpolation

    Hey MrShorty

    Great point! Ignore my latest question actually. If I want to find the sieve size at which 10% of the material passes, it does make more sense to use the 12%-6% range.

    Thanks for clearing that up for me

  13. #13
    Registered User
    Join Date
    04-11-2016
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    40

    Re: Interpolation

    Thanks for the help shg!

  14. #14
    Registered User
    Join Date
    04-11-2016
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    40

    Re: Interpolation

    I really appreciate everyone's help! You guys are Excel geniuses!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. 2D interpolation
    By o0tintin0o in forum Excel General
    Replies: 1
    Last Post: 04-02-2012, 03:41 PM
  2. Interpolation
    By rehan0129 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2010, 12:44 AM
  3. Replies: 0
    Last Post: 07-24-2008, 02:27 PM
  4. Interpolation
    By shantibala in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2008, 11:37 PM
  5. interpolation
    By atatari in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-23-2006, 04:10 PM
  6. [SOLVED] interpolation
    By atatari in forum Excel General
    Replies: 0
    Last Post: 02-12-2006, 11:50 PM
  7. 3D Interpolation
    By EStewart in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-01-2006, 11:55 AM
  8. [SOLVED] help with interpolation and limit of interpolation
    By uriel78 in forum Excel General
    Replies: 0
    Last Post: 02-18-2005, 10:06 AM

Tags for this Thread

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