Can you explain this Excel formula?

cybergibbons

Stompa wrote: »

Does that mean you would pass x-(100*y), but fail x-100*y ?

I mean, just a slightly more complex example. Here is a simple formula, where the person writing it has no knowledge of precedence, and by extension, no idea about how to re-arrange a formula:

(10*((100*x)-(50*y)-(50*z)))/w


Or you could write it like this:

( 2*x - y - z ) * 500 / w

DVardysShadow

Stompa wrote: »

Does that mean you would pass x-(100*y), but fail x-100*y ?

Absolutely.

With x-(100*y), there is no hesitation over the order of execution. But with x-100*y , does the coder mean (x-100)*y are they expecting the execution to be in order of appearance?

cybergibbons wrote: »

I mean, just a slightly more complex example. Here is a simple formula, where the person writing it has no knowledge of precedence, and by extension, no idea about how to re-arrange a formula:

(10*((100*x)-(50*y)-(50*z)))/w


Or you could write it like this:

( 2*x - y - z ) * 500 / w

The issue here is not a matter of precedence versus bracketting, it is that the person writing the code has no idea how to rearrange the expression for efficient computing

I would not let your streamlined version pass a code review either. The answer 'should' be the same regardless of the order of execution. But the fact that it is 500 and not 500.0 indicates that we are talking integer arithmetic - and the results WILL frequently be different according to execution order. Multiplication before division is likely to be the more accurate result, but the precedence order is division before multiplication.

Bear in mind this can be executed in 3 different ways

• [( 2*x - y - z ) * 500] / w
• ( 2*x - y - z ) * [500 / w ]
• [( 2*x - y - z ) / w] * 500

As shown, it is quite simple to add brackets to your expression to eliminate precedence and to define the non trivial parts of the order of execution.

DVardysShadow

cybergibbons wrote: »

Can you give a reference to a coding standard that says you should rely on parenthesis instead of operator precedence?

I can give references that state the opposite, and it's certainly not what I was taught to Masters level.

I personally think that explicitly stating the order that everything needs to be done in is unreadable and can stop the compiler fully optimising the code.

The standards I work to are not public domain. Personally, I don't care much for the academic referencing approach to resolving this particular issue, I prefer to deal with the explicit arguments, which I have mostly given explicitly or implied.

I think you were taught wrongly at Masters level [not claiming to be at that level]. The references might be interesting, but I do not think they will sway me.

On readability, I have dealt with some horrendous stuff, but I believe that the right way to deal with it is to break up the calculations and assign to intermediate variables. On compiler optimisation, I am quite unmoved. Compiler optimisation is for dealing with efficiently managing intermediate variables according to your choice of execution order. It is permissible for trivial parts of execution order:
eg x + y] - z] vs [x +[ y - z
but for the non - trivial parts, it is unacceptable to allow the compiler to make these decisions. If nothing else, it leads to code which is potentially non-portable to other compilers or hardware because the optimisations may be differently applied. I value consistency in running the code over 'efficiency' any day.

esuhl

Wig wrote: »

I'm surprised that this part (C4-100*INT(C4/100)) doesn't need to be written like this (C4-(100*INT(C4/100)))

How does it know that (C4-100*INT(C4/100))
(1234-100*12)

= 1234 - 1200

and not

=1134*12

?? it must be something to do with the respective priorities of minus and multiplication symbols? Like a multiplier always take priority over a minus sign - something I didn't know....

Yes - it is to do with operator precedence. Multiplication and division always take precedence over addition or subtraction. So the following are identical:

1234-100*12
1234-(100*12)

I tend to use unneccessary brackets to aid reading comprehension if the formula is long or complicated, but otherwise omit them. If I remember rightly you can only have 255 characters in an Excel formula, so sometimes it's useful to trim out as many characters as you can!

esuhl

DVardysShadow wrote: »

And when I review software, I will not pass code which relies on operator precedence.

So your students write valid code and you fail them for it?! In the classes I took, we were always expected to rely on operator precedence, rather than un-necessary brackets unless the brackets significantly aided readability. Having too many brackets in a formula can make it just as hard to understand as when there aren't any. But this is really just a stylistic preference.

DVardysShadow wrote: »

With x-(100*y), there is no hesitation over the order of execution. But with x-100*y , does the coder mean (x-100)*y are they expecting the execution to be in order of appearance?

There is no ambiguity here. x-100*y is identical to x-(100*y). A student shouldn't be failed for using the former notation unless their intention was to write (x-100)*y and they failed to understand operator precedence.

DVardysShadow

esuhl wrote: »

So your students write valid code and you fail them for it?! In the classes I took, we were always expected to rely on operator precedence, rather than un-necessary brackets unless the brackets significantly aided readability. Having too many brackets in a formula can make it just as hard to understand as when there aren't any. But this is really just a stylistic preference.

This is industrial. Training rather than academic and code which goes into real systems. I do not fail people, I fail code. Computed arithmetic is just an approximation to mathematical accuracy. As I pointed out above with integer arithmetic, the results can and do vary according to execution order. This is not stylistic. There are real differences in outcomes.

If you dislike all the brackets, then there is always the option of calculating intermediate results [except in spreadsheets, unless you add a row or column]. In the old days of FORTRAN with punched cards, possibly it was better to rely on precedence rather than on disambiguation with brackets. Possibly this is the basis of the references which cybergibbons relies on. But most of the old constriants have disappeared.

esuhl wrote: »

There is no ambiguity here. x-100*y is identical to x-(100*y). A student shouldn't be failed for using the former notation unless their intention was to write (x-100)*y and they failed to understand operator precedence.

Mathematically, they are identical. Computationally they are different. As I say, I don't fail students, but I will throw code back.

x-100*y is inherently ambiguous. If you choose to use it in code which only you work on, then it is down to you. But if it is commercial and industrial code worked on by more than 1 person, ambiguity and potential ambiguity should be eliminated.

esuhl

DVardysShadow wrote: »

Mathematically, they are identical. Computationally they are different... x-100*y is inherently ambiguous.

But... "mathematics" and "computation" are just different ways of saying the same thing. Computers have to obey the laws of mathematics otherwise they wouldn't be computing.

If a compiler processes addition before multiplication, then the fault is with the programmer who designed the compiler, not with the programmer who expected a mathematical computation machine to obey the laws of mathematical computation.

Have you really come across systems that don't obey mathematical laws?

fwor

I suspect that the main issue is where you are doing floating point operations. As no computer can work to infinite precision, you will always get rounding errors, and these errors will differ depending on the order in which you carry out operations.

In the real world I would guess that the differences will usually be tiny and insignificant, but there will nevertheless be differences.

DVardysShadow

esuhl wrote: »

But... "mathematics" and "computation" are just different ways of saying the same thing. Computers have to obey the laws of mathematics otherwise they wouldn't be computing.

If a compiler processes addition before multiplication, then the fault is with the programmer who designed the compiler, not with the programmer who expected a mathematical computation machine to obey the laws of mathematical computation.

Have you really come across systems that don't obey mathematical laws?

Yes, every single one of them approximates on floating arithmetic.

And as indicated in my earlier post, on integer artihmetic, mathematics itself yields different results according to the order of execution. Now, you could argue that the order of writing defines the order of execution - but if you do, then in integer arithmentic you would have to challenge a few things which mathematics defines as equivalent in real number arithmetic.

Stompa

DVardysShadow wrote: »

x-100*y is inherently ambiguous.

But only if you choose to ignore operator precedence.