Can you explain this Excel formula?

DVardysShadow

Stompa wrote: »

But only if you choose to ignore operator precedence.

I am confident I know operator precedence. I have never been confident that everyone who might at any time in the past, present or future work on a body of code would actually know it too.

So I want to see explicitly unambiguous code. I don't want something as important as this to be just implied. I want the coder to tell me what order he wants. It gives me far more confidence to see it than to try and make my mind up whether a colleague understands operator precedence and applies it correctly.

Again, in both integer and floating point arithmetic, the Division over Multiplication precedence can be perceived to be so weak it could be ignored, because in pure real number arithmetic as opposed to computer floating point arithmetic it does not make a jot of difference.

If I see the brackets, it closes off questions very quickly, whereas the questions linger if there might or might not be understanding of and a reliance on precedence.

RobTang

The code isn't inherintly ambiguous. (There are always pre-defined specs stating precedence, only it is stated that precedence is undefined is when you are forced to assume amniguauty)

A lack of understanding on the readers part does not make somthing ambiguous.

for example...
Assumption - I have no idea what brackets do.
Conclusion - Your brackey version is also ambiguous, because I can interpret it however I want.

Somthing that is actually ambiguous would be what the OP is trying to enter into an excel cell say
"10:10" even if we say this value is a timespan
it could be 10 hours and 10 mins OR 10 mins and 10 seconds. This is actually ambiguous for excel's implmentation of timespan, which is why it forces you to state the full hh:mm:ss

So anyway DVardysShadow whats your opinion on dynamicly typed languages ?

DVardysShadow

RobTang wrote: »

The code isn't inherintly ambiguous. (There are always pre-defined specs stating precedence, only it is stated that precedence is undefined is when you are forced to assume amniguauty)

A lack of understanding on the readers part does not make somthing ambiguous.

But a lack of understanding on the part of the Author makes it inherently ambiguous. OK, it can be said to have an unambiguous interpretation. But if you doubt the Author's understanding of precedence, you must doubt that the code matches the Author's intent.

Don't forget that code is required to communicate in 2 directions - to a compiler or run-time environment and to a human reading it. Regardless of the precedence rules implemented by the compiler, strong account should e taken of the human aspect - otherwise we would still all be writing assembler.

RobTang wrote: »

So anyway DVardysShadow whats your opinion on dynamicly typed languages ?

If you must do it, then duck typing is a good idea.

esuhl

fwor wrote: »

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.

Good point, but since there is no ambiguity in operator precedence (i.e. the computer doesn't arbitrarily choose precedence) the rounding error will not be random - it will obey the implicit mathematical laws of floating point calculations. In other words we should remember that "X + Y" actually means "X + Y to n significant figures".

DVardysShadow wrote: »

Don't forget that code is required to communicate in 2 directions - to a compiler or run-time environment and to a human reading it.

The arcane use of code should be avoided, but there's no need to use un-necessary brackets or un-simplified formulae in code. If anything might benefit from further elaboration, that's what comments are for.

One piece of code I wrote (and I haven't written much) took over 8 hours to execute because it was inefficient (but readable). Mathematically simplifying the expressions, and understanding how the code was compiled (so I could avoid creating inefficient machine code) made much of the code quite horrific to look at, but cut the execution time down to under 2 hours. The comments explained what was happening (but of course had no effect on execution as the compiler stripped them out).

DVardysShadow

esuhl wrote: »

The arcane use of code should be avoided, but there's no need to use un-necessary brackets or un-simplified formulae in code. If anything might benefit from further elaboration, that's what comments are for.

By definition, there is no need to use unnecessary brackets. But just because the compiler does not need them, it does not mean that they are unnecessary.

:eek::eek::eek: About the last thing I would use comments for is to avoid the use of brackets. As I have said, I would take an intermediate result if the brackets were getting too much. And I think I would take more intermediate results using precedence rather than brackets.

john_s_2

This is an interesting debate! Personally I hate unnecessary brackets in code and sometimes find myself carefully removing them from a formula so I can read it. Especially when you have about five brackets in a row, eg (((((x+1)*2)+3)*4)+1)

The same goes for wrapping ANDs in brackets as well.

Point taken about long formulas should perhaps be broken up and use intermediate variables instead.

Reading this debate suggests that people fall in to two camps: those who like extra brackets and those that don't. If I ruled the world I know which one I'd force people to use. But I don't. Not yet, anyway ;-)

cybergibbons

DVardysShadow wrote: »

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.

I was being loose with my previous formula, and you implied I was using integers, which wasn't my intention.

Nonetheless, my feeling, and teaching, is that calculations involving integers and division should be dealt with as floats (hardware limitation notwithstanding), and then brought back to integers at the end.

tomstickland

=(INT(C4/100)+(C4-100*INT(C4/100))/60)/60/24

INT (C4/100) gives you the integer part of C4/100
ie: picks out any digits greater than 100

C4 - 100 * INT (C4/100)
is the remainder, which is any values smaller than 100
it's then divided by 60 to convert from seconds to minutes.

the whole lot is then divided by (60 x 24)
ie: number of minutes in a day. This is so that it meets the Excel definition of time being in fractions of a day.

tomstickland

Stompa wrote: »

I think I would have gone for the slightly simpler:

=(60*INT(C4/100)+MOD(C4,100))/86400

or perhaps the more readable:

=TIME(0,INT(C4/100),MOD(C4,100))

/8600 is bad practice though - it's a "magic number". Better practice to have "24 * 60"

tomstickland

Quote:
Originally Posted by Stompa
Does that mean you would pass x-(100*y), but fail x-100*y ?

Absolutely.

I've worked in mathematical software for years and it's absolutely assumed that people understand mathematical precedance.
x-100*y is totally clear and there is nothing ambigious about it.