Can you explain this Excel formula?

RobTang

DVardysShadow wrote: »

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.

You cant say, my programmers are idiots therefore their code must be idiotic.

Again I doupt somone knows how to use brackets so the meaning of
x-(100*y) is unclear.
To avoid confusion they must use
a = 100 * y
b = x -a
I can go even further and doupt they know how operators actually work in their given language (somthing most really wont know) and ban their use as well.

DVardysShadow wrote: »

And I think I would take more intermediate results using precedence rather than brackets.

Just because YOU need clarification, doesn't mean the intermediate steps are needed, by defination you can write any single experssion and rely on precedence and it would be the same as the brackets version.

DVardysShadow

tomstickland wrote: »

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

8600 -> 86400.

Why do at runtime what can be done at coding time?

I agree it is a magic number, but you deal with that by defining a variable

SECONDS_IN_DAY = 86400

Additionally note that 24 and 60 are magic numbers.

DVardysShadow

tomstickland wrote: »

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.

This is exactly what I complained about. You are conflating software with mathematics. You come at this as a mathematician. Code is not mathematics.

Mathematicians understand precedence. Straight BODMAS precedence is fairly OK - but there is a large heirarchy of other operators. What is the precedence between % and / for example? Yes, you can look it up, but there are too many operators.

And the visual effect of precedence gets worse when you use meaningful variable names.

Stompa

DVardysShadow wrote: »

8600 -> 86400.

Why do at runtime what can be done at coding time?

If it's a compiled language then the compiler will almost certainly (by default) perform the 24*60 calculation at compile time rather than runtime anyway.

DVardysShadow

Stompa wrote: »

If it's a compiled language then the compiler will almost certainly (by default) perform the 24*60 calculation at compile time rather than runtime anyway.

But not with an interpreted language.

However the important point is to make scrutinisable the Magic Numbers which Tom mentioned

tomstickland

If it said "8654492" and then had a comment like "this is the number of seconds in a day" then that'd be fine.

Re mathematics versus programming language. A properly designed compiler will have precedence rules built in. If it's some flaky crap then I could see the benefit of extra brackets.

DVardysShadow

tomstickland wrote: »

If it said "8654492" and then had a comment like "this is the number of seconds in a day" then that'd be fine.

Re mathematics versus programming language. A properly designed compiler will have precedence rules built in. If it's some flaky crap then I could see the benefit of extra brackets.

People are more flaky than compilers - and with several dozens of operators, when you go outside arithmetic, the chances of getting it right tend away from 100%

Stompa

DVardysShadow wrote: »

But not with an interpreted language.

Indeed.

DVardysShadow wrote: »

However the important point is to make scrutinisable the Magic Numbers which Tom mentioned

True. My reason for making it non-magic in this instance was that I thought that formulae in Excel were interpreted rather than compiled. I could well be wrong though, does anybody know for sure?

DVardysShadow

Stompa wrote: »

...

True. My reason for making it non-magic in this instance was that I thought that formulae in Excel were interpreted rather than compiled. I could well be wrong though, does anybody know for sure?

I would go with interpreted. If you look at the spreadsheet file, there is only the one, so no executable binary.

I don't dabble with excel, but if I were to do an industrial strength spreadsheet, I think I would put Magic Numbers on a hidden page of their own, with descriptions. And I would be looking for a Macro facility or possibly even some built in predefined values - I would be very surprised if you could not predefine some constants.

Nukumai

tomstickland wrote: »

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.

Agree 100%. I, too, have worked with developing and using analytical / mathematical software for a good many years (trading and risk management in the banking sector) and would have absolutely no tolerance for a programmer who did not have a full grasp of mathematical operator precedence. There is just no excuse.

In fact, I am astounded at some of what I have read in this thread (eg. the insistence upon parenthesis at academic level).