One of the questions I am sometimes asked is, “If I need a specific x-value to appear in my graphical function, how do I determine the number of points required to include that exact point?” This arises because in a graphical function, the x-axis is divided into fixed intervals; the user is only allowed to specify x-min, x-max, and the number of data points. [If you really need a graphical function with arbitrary (x, y) points, use the LOOKUPXY() built-in function available in version 9.1.2.]
For the following discussion, the range on the x-axis will be needed:
range = x_max – x_min
In all of the examples below, x_min = 3.000 and x_max = 7.000, so the range = 7 – 3 = 4.
For integer values, here is a simple formula that always works:
# of data points = range + 1 (1)
For example (using the range above), if the number 6 (an integer) must appear as a point on the x-axis, 4 + 1 = 5 points will be needed. This divides the x-axis into the points 3.0, 4.0, 5.0, 6.0, and 7.0.
Fractional values, however, are more difficult. In general, if you can isolate the fractional part (and this evenly divides the range), the maximum number of points needed is given by this formula:
# of data points = range/fraction + 1 (2)
Note for integers, which have a fraction of one, this just simplifies to formula (1). For example, suppose that the number 4.333 must appear on the x-axis. The fractional part of this value is 0.333, or 1/3, so 4/(1/3) + 1 = 4*3 + 1 = 13 points will be needed. This divides the x-axis into 3.0, 3.333, 3.667, 4.0, 4.333, 4.667, etc.
Note, however, that this is not the smallest number of data points needed. In fact, four points are sufficient: 3.0, 4.333, 5.667, 7.0. The problem with fractional values is that the equivalent of the least common divisor (LCD) must somehow be found (I leave this derivation to the reader). The following formula, when it evaluates to an integer, gives this:
# of data points = range/(desired_point – x_min) + 1 (3)
For the above example,
range/(desired_point – x_min) + 1 = 4/(4.3333 – 3.0) + 1 = 4/1.3333 + 1 = 3.000 + 1 = 4
The number 4/1.3333 = 3 is important. The desired value 4.333 will appear in the set of x-axis points whenever the number of data points is set to any of 3k + 1, where k = 1, 2, 3, … This series contains the values 4, 7, 10, 13, 15, … Any of these values can be used for the number of data points and 4.333 will appear somewhere on the x-axis.
There are more difficult cases. If 4.3 is desired on the x-axis and 4.333 is just not close enough, the first formula yields 4/0.1 + 1 = 41 data points. Note 0.3 could not be used as the fractional part because it does not go evenly into 4. The largest factor of 0.3 that does go into 4 is 0.1, hence 0.1 is used in the formula.
Is there a smaller number of data points that might work? Formula (3) can help here:
range/(desired_point – x_min) + 1 = 4/(4.3 – 3) + 1 = 4/1.3 + 1 = 3.077 + 1 = 4.077
This does not look promising as it is not an integer. In fact, there is no value for the number of data points less than 41 that will work in this case. Note that all values 40k + 1 will work (for k = 1, 2, 3, …).