title: Linear interpolation gives error at specific input values between 70% and 80%
body: <p>I have a very specific excel problem. I have a model where a certain <em>boost</em> leads to a certain <em>rate</em>. These boosts are defined as 10%, 20%, 30% until 100%. To be able to determine what the rate will be at, for instance, 75%, I want to use linear interpolation. </p>
  
  <pre><code>Boost  Rate 
  0%      1.50%
  10%     1.75%
  20%     2,00%
  30%     2.25%
  40%     2.50%
  50%     2.75%
  60%     3.00%
  70%     3.25%
  80%     3.50%
  90%     3.75%
  100%    4.00%
  </code></pre>
  
  <p><strong>For the sake of simplicity, these rates are linear distributed, but in reality they are not. I only want to interpolate between the 10% intervals.</strong> </p>
  
  <p>I use a combination of VLOOKUP and the ROUNDUP/ROUNDDOWN functions to make a linear interpolation. For instance: When the boost is 56%, I first rounddown 56% to 50% and use VLOOKUP to retrieve the rate from the table, which returns 2,75%. Then I ROUNDUP 56% to 60% and also retrieve the corresponding rate for that boost, which is 3,00%. Then I take the 3%, substract 2,75% and multiply the outcome by (56% - 50%) / 10%. This results in 0.15%. When I add this to the 2.74% retrieved early, I have the result: 2.90%.</p>
  
  <p>In Excel formulas, it looks like this:</p>
  
  <pre><code>=((D5-ROUNDDOWN(D5;1))/0,1)*(VLOOKUP(ROUNDUP(D5;1);$A$2:$B$12;2;0)-VLOOKUP(ROUNDDOWN(D5;1);$A$2:$B$12;2;0))+VLOOKUP(ROUNDDOWN(D5;1);$A$2:$B$12;2;0)
  </code></pre>
  
  <p>Where D5 = the boost percentage you want to get the rate for. </p>
  
  <p>So far so good. This works, <em>except</em> for boosts between 70% and 80% <strong>and i just don't understand why.</strong></p>
  
  <p>This is the output I get:</p>
  
  <pre><code>Boost   Rate
  53%     2.83%
  54%     2.85%
  55%     2.88%
  56%     2.90%
  57%     2.93%
  58%     2.95%
  59%     2.98%
  60%     3.00%
  61%     3.03%
  62%     3.05%
  63%     3.08%
  64%     3.10%
  65%     3.13%
  66%     3.15%
  67%     3.18%
  68%     3.20%
  69%     3.23%
  70%     3.25%
  71%     #N/A
  72%     #N/A
  73%     #N/A
  74%     #N/A
  75%     #N/A
  76%     #N/A
  77%     #N/A
  78%     #N/A
  79%     #N/A
  80%     3.50%
  81%     3.53%
  82%     3.55%
  83%     3.58%
  84%     3.60%
  </code></pre>
  
  <p>I have combed through the internet trying to find a solution, but it's not there. Does anyone know how to this is happening or does anything have an alternative solution?  </p>