The difference of results between VB and excel in power operation and its irrationality

excel| Operation Nefu Environmental Science Department Shangxiang
Introduction
Expression parsing is one of the most basic functions of software, such as programming tools and spreadsheets, and different software follows certain operation rules when parsing expressions, and in different software, these operation rules are slightly different, resulting in the difference of calculation results. In practice, I found that VB and excel in the process of "continuous power" of this common operation, followed by the law has a large discrepancy, and there are unreasonable factors. In this paper, we try to get a more reasonable operation mode and provide guidance for the process of writing expression parsing class.

Problem analysis
The following table lists the results of VB6.0 and Excel2003 when they handle the same expression about the exponentiation operation.

Table 1 Differences in the results of the VB6.0 and Excel2003 performed by the exponentiation operation

An expression
VB calculation results
Excel Calculation results

2^-2^-2
.840896415253715
16

-2 ^ 2
-4
4

-2^-2^-2
-.840896415253715
16

2 ^-3 ^ 4 ^ 7
0
5.16988E-26


I have analyzed that the main factor that caused the difference of results is the operation order of the monocular operator "-" and successive power. Detailed analysis is as follows:

1. The calculation step for the expression 2^-2^-2,vb is 2^ ( -2^-2) =. 840896415253715, at which point it is computed from right to left, while Excel strictly follows the order of the binding from left to right. If we understand the 2^-2^-2, then, obviously the results of VB calculation is reasonable, but not VB in the processing of continuous operation is from right to left? The answer is in the negative. In the calculation of 2^3^4, the result of VB is 4096 instead of 2.41785163922926E+24, which shows that at this time, VB did not understand 2^3^4 understanding as, is from left to right of the binding order. Obviously, VB in the processing of the exponentiation, the combination of the order does not have a compelling basis, and there is no rationality.

2. For the expression -2^2,VB calculation process is equivalent to-(2^2) =-4, the exponential operator (^) is higher than the negative operator (-), as described in the article "operator precedence" in the Microsoft MSDN 6.0 Simplified Chinese version of the VB Help system. So it seems that VB in accordance with such a law to get such a result is reasonable. For Excel, its calculation is equivalent to (-2) ^2, and the symbol (-) is higher than the power (^) in the version of the Excel 2003 Help document, and the Chinese name of the two software operator is slightly different, so it seems that Excel is also reasonable. However, there is a problem with Excel's approach to placing the "-(minus)" priority before the "^" operator. For example, the result of an expression 4-2^2, 4--2^2, 4---2^2, 4-......-(n-) 2^2 is 0, which is not easily explained by the above law.

3. The results of the calculation of the expression -2^-2^-2 are different due to the above two reasons.

4. For the expression 2 ^-3 ^ 4 ^ 7,vb calculated as a result of 0, this is in any case not smooth explanation. The origin of this result has yet to be further studied.

The suggestion of expression parsing law
In view of the differences in the results of the power operation between VB and Excel when parsing the expression, it is suggested that the following principles should be followed when writing an expression parsing process:

1. It is recommended that the power operator "^" be given precedence over the Monocular negative operator "-".

2. The combination of the power operation should be unified, it is recommended to use a combination of left to right.