excel bug ???

in Excel, the following formula yield different results. Why, and which is correct?

{=IF(G13:G69+(($F$13-F13:F69)*TAN($A$10))>0,G13:G69+(($F$13-F13:F69)*TAN($A$10)),0)}

{=IF(G13:G69+(($F$13-F13:F69)*TAN($A$10))>0,G13:G69+($F$13-F13:F69)*TAN($A$10),0)}

The extra set of parentheses should be of no consequence. You don't want to know what I went through to figure this out. The difference is rather small, but important in an itteration setting. Errors on the order of 1/1000th.

in the above example, the second line was the original, but the first condition was getting evaluated to a different value than the supposedly identical result if true. The expression lacking the extra set of parentheses yields a smaller result.


I'm still trying to puzzle this out myself, but if anyone has any Parentheses Protocols for excel I would appreciate it.

 

As a start, here is the operator precedence.

My intuition (as a former software developer in C++) is that you are getting a rounding error that is being set off by the varying order of parenthesis in some way.

Are you sure the number being displayed in your Excel cell is the actual value, out to the number of decimal places required, or could it be a display rounding error? Just a thought, but it seems to be maybe a rounding error, as most true errors would be more significant values.

Based on the order of things in your post, it would seem the parenthesis should make no difference. That is, the tangent value should be multiplied by the F13/69 expression, then added to the G13/69 expression. Then, all of that should be entered as a value in the 0,expression,0 side of the inequality. That ought to happen with either parenthesis you are showing.

Are you finding that the overall IF statement returns TRUE or FALSE unexpectedly? What are your requirements for accuracy (significant figures), remembering that Excel works more like a calculator in that it can return sig figs that are way out there... and sometimes rounds the last couple digits in my experience. If your need for sig figs isn't too great, you can always round the rounding error itself to make sure it is going to be a value you expect at all times... depending on the exact application of this formula, of course.

OPERATOR PRECEDENCE
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication and division operator — Excel evaluates the operators from left to right.

OPERATOR DESCRIPTION
: (colon)
(single space)
, (comma)
Reference operators
– Negation (as in –1)
% Percent
^ Exponentiation
* and / Multiplication and division
+ and – Addition and subtraction
& Connects two strings of text (concatenation)
=
< >
<=
>=
<> Comparison
USE OF PARENTHESES
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.

=(5+2)*3

In the example below, the parentheses around the first part of the formula force Excel to calculate B4+25 first and then divide the result by the sum of the values in cells D5, E5, and F5.

=(B4+25)/SUM(D5:F5)

 

Okay, it may have to do with the dependant variables getting updated between the IF expression and the THEN expression. I'm using a mixture of columns, some imputted as array and some not. Something to do with the order of sheet calculations. Probably nothing to do with the parentheses. Damned annoying, what ever it is.

 

And what, exactly is the IF statement doing? I see it asking if a single numerical value is greater than an array (of 3 numerical values).

Is that correct? It looks like two different data types to me, but my experience is more in software development in a scientific setting than figuring out how Microsoft decided to implement little details in Excel.

 

That would make sense.... if globals were somehow being updated between the IF and THEN. You should be able to isolate that down... But shouldn't have to! Excel can be so annoying!

Good luck with it.

 

replaced the array formulas with nonarrays and problem went away. I guess it's all one or the other.

 

That makes sense. You can't ask if 3 values (in an array) are greater than a single value. That makes no logical sense. If I recall, that would produce a type mismatch error in c++. Excel should have thrown a similar error to clue you in if it was a well written program. Glad it is now working.

 

Phil,

1. In Excel, the tangent value of an angle is in radians. You must convert first the angle to radians.
2. The Term G13:G69 is not recognized. It should be written as =SUM(G13:G69) unless you are looking for a unique value in the range.
3. Whenever you are in doubt about the results, always break it down first into terms/cell and apply the formula/expression/answer in another cell with a corresponding operations sequence. That way you will be able to troubleshoot which cell or terms is giving you problem. After you are already confident about the result, then you can write the complex formula in another cell, erase the trial cells and move your complex formula.
4. You must have written a typo error in there as you are comparing the result of the FIRST expression with the result of the FIRST expression. You will get the same answer everytime. You can use a simpler Expression rather than IF.
5. I think you are using too much parenthesis. Try the order of operations CB suggested.

Attached Excel spreadsheet as it is better to understand when it is in the spreadsheet.

Note: In the spreadsheet, I did not covert the angle to radians but showed in cell A11:A12 how it is done.

 

Ok. Got it. You are testing the first expression if it is greater than 0, then if TRUE, you write the first expression, otherwise if FALSE, write "0". My error.