Excel – Creating chart in Excel where data for X-axis (horizontal) is non linear and extrapolating value

chartsmicrosoft excelmicrosoft-excel-2007microsoft-excel-2010

Have some data of the following form:

  X |  Y
----+-----
  3 |  42
  4 |  57
  6 |  92
 11 | 119
 19 | 316

(NOTE: The above table is made-up data)

What I'd like to do with MS-Excel is to plot 2 curves, one that treats values in col. 'X' as horizontal axis data, assuming it to be a linear scale, and another assuming it to be a logarithmic scale. Is this possible ? If so, how ?

Also, if I wish to extrapolate the value of Y, (using, what I hope would be a choice of multiple "models"), for a given value of X, s.a. X = 5 or X = 9, how can I do it ?

I'd believe this to be the typical curve-fitting problem, with the statistical extrapolation, but don't know enough of MS-Excel to get around.

Best Answer

You've asked two different (albeit related) questions here. You should consider splitting this into two separate questions.

In answer to your first:

  1. Create your chart as a Scatter (XY) Type.
  2. Adjust your axis (for min/max limits) and any other formatting you desire.
  3. Select this first chart and choose copy.
  4. Paste a copy of your first chart on your worksheet.
  5. On your second chart, select your horizontal axis, then choose Format Selection.
  6. In the Axis Options window, check the Logarithmic Scale box and enter your Base value (default is 10). Log scales are not available in the basic line chart or area chart options.

You now have two identically formatted charts, showing the same data with the difference in how the horizontal scale is displayed, like this:

enter image description here

In short answer to your second, this is very easy to do in Excel. The simplest method would be to setup a table with your base X values (e.g. as you describe above), a "model cell", and in place of static Y values, you'd have a formula (e.g. X*mod = Y). You would then just plot the Y values, which would change whenever you updated the value in your "model cell". This can get pretty complex and sophisticated with some work (e.g. using a lookup table to preset the "model cell" values, or using contingency tables). But as I started this answer, this is really a second question that requires additional detail from you to properly answer.

Related Question