Vba Issue With Mid String Function For Excel 2004 For Mac
As with other spreadsheets, works only to limited accuracy because it retains only a certain number of figures to describe numbers (it has limited ). With some exceptions regarding erroneous values, infinities, and denormalized numbers, Excel calculates in from the (besides numbers, Excel uses a few other data types ). Although Excel can display 30 decimal places, its precision for a specified number is confined to 15, and calculations may have an accuracy that is even less due to three issues:,. Of course, 1 + x − 1 = x. The discrepancy indicates the error. All errors but the last are beyond the 15-th decimal. In the top figure the fraction 1/9000 in Excel is displayed.
Although this number has a decimal representation that is an infinite string of ones, Excel displays only the leading 15 figures. In the second line, the number one is added to the fraction, and again Excel displays only 15 figures.
In the third line, one is subtracted from the sum using Excel. Because the sum has only eleven 1's after the decimal, the true difference when ‘1’ is subtracted is three 0's followed by a string of eleven 1's. However, the difference reported by Excel is three 0's followed by a 15-digit string of thirteen 1's and two extra erroneous digits. Thus, the numbers Excel calculates with are not the numbers that it displays. Moreover, the error in Excel's answer is not simply round-off error. The inaccuracy in Excel calculations is more complicated than errors due to a precision of 15 significant figures.
Excel's storage of numbers in binary format also affects its accuracy. To illustrate, the lower figure tabulates the simple addition 1 + x − 1 for several values of x. All the values of x begin at the 15-th decimal, so Excel must take them into account. Before calculating the sum 1 + x, Excel first approximates x as a binary number. If this binary version of x is a simple power of 2, the 15-digit decimal approximation to x is stored in the sum, and the top two examples of the figure indicate recovery of x without error. In the third example, x is a more complicated binary number, x = 1.110111⋯111 × 2 −49 (15 bits altogether). Here x is approximated by the 4-bit binary 1.111 × 2 −49 (some insight into this approximation can be found using: x = 1.11 × 2 −49 + 2 −52 × (1 − 2 −11) ≈ 1.11 × 2 −49 + 2 −52 = 1.111 × 2 −49 ) and the decimal equivalent of this crude 4-bit approximation is used.
In the fourth example, x is a decimal number not equivalent to a simple binary (although it agrees with the binary of the third example to the precision displayed). The decimal input is approximated by a binary and then that decimal is used. These two middle examples in the figure show that some error is introduced. The last two examples illustrate what happens if x is a rather small number. In the second from last example, x = 1.110111⋯111 × 2 −50; 15 bits altogether.
Vba Issue With Mid String Function For Excel 2004 For Mac Mac
The binary is replaced very crudely by a single power of 2 (in this example, 2 −49) and its decimal equivalent is used. In the bottom example, a decimal identical with the binary above to the precision shown, is nonetheless approximated differently from the binary, and is eliminated by truncation to 15 significant figures, making no contribution to 1 + x − 1, leading to x = 0. For x′s that are not simple powers of 2, a noticeable error in 1 + x − 1 can occur even when x is quite large. For example, if x = 1/1000, then 1 + x − 1 = 9.9 89 × 10 −4, an error in the 13-th significant figure.
In this case, if Excel simply added and subtracted the decimal numbers, avoiding the conversion to binary and back again to decimal, no round-off error would occur and accuracy actually would be better. Excel has the option to 'Set precision as displayed'. With this option, depending upon circumstance, accuracy may turn out to be better or worse, but you will know exactly what Excel is doing. (It should be noted, however, that only the selected precision is retained, and one cannot recover extra digits by reversing this option.) Some similar examples can be found at this link. In short, a variety of accuracy behavior is introduced by the combination of representing a number with a limited number of binary digits, along with numbers beyond the fifteenth significant figure. Excel's treatment of numbers beyond 15 significant figures sometimes contributes better accuracy to the final few significant figures of a computation than working directly with only 15 significant figures, and sometimes not.
For the reasoning behind the conversion to binary representation and back to decimal, and for more detail about accuracy in Excel and VBA consult these links. Examples where precision is no indicator of accuracy. Excel graph of the difference between two evaluations of the smallest root of a quadratic: direct evaluation using the quadratic formula (accurate at smaller b) and an approximation for widely spaced roots (accurate for larger b).
The difference reaches a minimum at the large dots, and round-off causes squiggles in the curves beyond this minimum. The bottom line is that in doing this calculation using Excel, as the roots become farther apart in value, the method of calculation will have to switch from direct evaluation of the quadratic formula to some other method so as to limit round-off error. The point to switch methods varies according to the size of coefficients a and b. In the figure, Excel is used to find the smallest root of the quadratic equation x 2 + bx + c = 0 for c = 4 and c = 4 × 10 5. The difference between direct evaluation using the quadratic formula and the approximation described above for widely spaced roots is plotted vs.
Initially the difference between the methods declines because the widely spaced root method becomes more accurate at larger b-values. However, beyond some b-value the difference increases because the quadratic formula (good for smaller b-values) becomes worse due to round-off, while the widely spaced root method (good for large b-values) continues to improve. The point to switch methods is indicated by large dots, and is larger for larger c -values. At large b-values, the upward sloping curve is Excel's round-off error in the quadratic formula, whose erratic behavior causes the curves to squiggle. A different field where accuracy is an issue is the area of and the.
Examples are, the, and the Numerov algorithm for the. Using Visual Basic for Applications, any of these methods can be implemented in Excel. Numerical methods use a grid where functions are evaluated. The functions may be interpolated between grid points or extrapolated to locate adjacent grid points. These formulas involve comparisons of adjacent values.
If the grid is spaced very finely, round-off error will occur, and the less the precision used, the worse the round-off error. If spaced widely, accuracy will suffer. If the numerical procedure is thought of as a, this calculation noise may be viewed as a signal that is applied to the system, which will lead to instability unless the system is carefully designed.
Accuracy within VBA Although Excel nominally works with numbers by default, has a variety of data types. The Double data type is 8 bytes, the Integer data type is 2 bytes, and the general purpose 16 byte Variant data type can be converted to a 12 byte Decimal data type using the VBA conversion function CDec. Choice of variable types in a VBA calculation involves consideration of storage requirements, accuracy and speed.
References.
Excel VBA Programming - The Left and Right functions Excel VBA Programming. Excel VBA Left and Right functions The Left and Right functions are used to chop characters from a string. Use Left to chop characters from the start of the string; use Right to chop characters starting from the end of the string. In between the round brackets of Left and Right you type the number of characters you want to chop. If you miss out the number of characters to chop then Left and Right extract just one character from the start or end of the string.
Some example might clear things up. Create a new Sub and try this code out (you can use your spreadsheet from for this: Dim Email As String Email = 'myaddress@myisp.com' MsgBox Left(Email, 9) MsgBox Right(Email, 9) The first two lines just set up a String variable and place an email address in the Email variable. The third line is a message box that uses the Left function: MsgBox Left(Email, 9) When you run the code you'll see that the message box displays the first 9 characters of the email address, everything to the left of the @ sign. The fourth line is this: MsgBox Right(Email, 9) The Right function will display 9 characters starting from the final character in the email address, everything to the right of the @ sign. That's fairly straightforward, we're sure you'll agree. But now for a more complex use of Left and Right. Suppose you have a full name in cell A1 in this format: David Gilmour However, suppose you want to have the surname first then the first name.
This format: Gilmour, David You can use Left, Right and the InStr Functions to achieve this. Create a new Sub and call it LastFirst.