Author Topic: =SUM(A23/B21)  (Read 11461 times)

Kim

  • Timelord
    • Fediverse
Re: =SUM(A23/B21)
« Reply #75 on: 28 January, 2016, 11:54:46 pm »
To be fair, bitwise operations only really come in trivial or cryptic.

When I wrote the scrolly LED matrix sign code for our new alerting system, I examined my old code for determining whether a given combination of time and temperature (the default standby display) was numberwang[1].  It was, of course, completely undocumented, and full of comparisons and bitwise operations on the char values of the individual digits.  I have literally no idea what I was thinking at the time, but it was surely differently-sensible.

So I did what any reasonable person would do in the circumstances:  Copy-pasted the lot into an isNumberwang() function and backed away slowly.

Does Excel include a functioning for determining numberwang yet?  It really ought to.


[1] If it's numberwang, it changes the text colour in celebration.  This was a present for barakta some years ago, to save her the effort of doing it in her head.

Dibdib

  • Fat'n'slow
Re: =SUM(A23/B21)
« Reply #76 on: 29 January, 2016, 12:07:29 am »
Does Excel include a functioning for determining numberwang yet?  It really ought to.

It does, but the rules are out of date and it doesn't have the Mornington Crescent appendix.

Vince

  • Can't climb; won't climb
Re: =SUM(A23/B21)
« Reply #77 on: 29 January, 2016, 01:45:39 am »
Does Excel include a functioning for determining numberwang yet?  It really ought to.

It does, but the rules are out of date and it doesn't have the Mornington Crescent appendix.
Numberwang is Mornington Crescent, but with the street names numerically encoded.
216km from Marsh Gibbon

ElyDave

  • Royal and Ancient Polar Bear Society member 263583
Re: =SUM(A23/B21)
« Reply #78 on: 29 January, 2016, 09:18:07 am »
tbh I really wouldn't have a clue where to start for any real "programming" these days, the times when I could write a macro in BASIC are long gone, and the last real stuff I did was at university simulating reactors, heat exchangers, control valves etc.

The last tinkering I did was with a macro extracting stuff from a spreadsheet when the source software updated and changed its output format.

The one thing I have generally done and you could call it programming, is to use excel to either do umpteen calculations quickly, or to allow me to repeat complex fire/explosion/toxic cloud etc calculations rather than back of fag packet versions each time.  All of those were written up properly as papers, with sources for the equations and annotated spreadsheets as I knew others would come and use them after me.

Needless to say I have copies of them all.
“Procrastination is the thief of time, collar him.” –Charles Dickens

Feanor

  • It's mostly downhill from here.
Re: =SUM(A23/B21)
« Reply #79 on: 29 January, 2016, 09:26:22 pm »
To be fair, bitwise operations only really come in trivial or cryptic.
[...]
So I did what any reasonable person would do in the circumstances:  Copy-pasted the lot into an isNumberwang() function and backed away slowly.

That's exactly what I did with my 20-year old C code.
I Copy-pasted it into my Visual Studio C# project, and fixed up the types and syntax as required.

Well, not quite copy-paste.
I had archive on 5-1/4 floppy, and papyrus hard-copy.
Guess what was the most useful.

After re-reading it a few times, it did come back to me exactly what I was doing, but I CBA documenting it now.
"It was hard to write, so it should be hard to understand".
Even for the original author!

What the code does is to use  AND masks to seperate out the 3 components of the original xs-64 format
( which has been loaded as a dumb 32-bit unsigned Int to allow me to bit-twiddle them. )
The sign bit, the exponent, and the mantissa.
It then bit-shifts these 3 into a position I need to work with them.
I then do magic bit-wise manipulations on these components, to perform the actual translation between formats.
I then shift the results into their final positions, do an AND to mask out any spurious bits, and then OR the components back together into a final result.
Then I use an arcane thing called a Union which allows me to tell C / C#  to put on different glasses and view these 4 bytes which were previously a UInt type as a Float type.

It's the most wonderfully arcane bit of obfuscated C.



contango

  • NB have not grown beard since photo was taken
  • The Fat And The Furious
Re: =SUM(A23/B21)
« Reply #80 on: 30 January, 2016, 04:06:09 am »
I guess you never read the essay "how to write unmaintainable code" :)

I wrote some code this week as a freebie for some potential customers, who had some ancient data files in a long-forgotten format which they would need to import.
The long-forgotten format was one I had used, 20 years ago, and I still had the file format spec documents in storage!

So I dug out the Account of Isildur from my archive, and set-to hacking together a utility that would read these old data files and output them in in a current format.

One issue was that the data values were stored in a 32-bit floating point format called xs-64 which was used by the Perkin-Elmer mini-computers that wrote these files.   This is not the same format as IEEE-754 32-bit floats used in PCs.   I have been here before, in a previous life, and have code I wrote 20 years back that does bit-wise magic to convert the xs-64 to IEEE-754 format.

In my fresh code this week, my comments are:

//Begin: deep magic.

There follows deeply cryptic bit-wise operations.
Any-one who wishes to decode it is on their own!

Many years ago I had one of the users on a trading desk come to me in a panic. A spreadsheet had crashed and she needed it fixed before the end of the day. This was about about 4pm, and it was a spreadsheet I didn't even know existed (at the time I worked for the IT group that specifically supported that particular desk, so we knew about every official application they had because we wrote them). So I went to see her, looked at this mysterious spreadsheet and had to tell her there wasn't a hope in hell of getting it working that day and I couldn't even guarantee fixing it by the end of the week. There were pages and pages of code, totally undocumented, written by one of the traders with no indication of what he was doing or why.

Anyway I got to reading through the code and it essentially ran a simple query against the trading database and then did endless processing of the results, copying them to one worksheet after another. It turned out the trader who wrote it didn't know that SQL had a handy GROUP BY clause, so had written it himself using huge amounts of inefficient code. And because he wasn't used to writing robust code he hadn't bothered to do little things like check whether a trading book existed before downloading it.

I did get it back to the user, working much faster, before the end of the week. I replaced hundreds of lines of inefficient code with a few dozen lines of code, and along the way made sure it would report based on the books that were actually there when the report was produced rather than the books that were there when the code was written.


A piece of code I always felt guilty about handing over to my successor was truly horrible. Because of the nature of it, it was essentially four nested for-next loops. But depending on just what was being done the code execution might jump into one of the inner loops, using a state variable to jump back out again so it didn't hit an unexpected NEXT statement (this was in Access Basic, back when Access 2.0 was considered cutting edge). I tried to explain to him what the code did and why I'd done it that way and in the end told him to just leave it alone so as not to break it, and that if anybody changed it and it broke I didn't want to know about it.
Always carry a small flask of whisky in case of snakebite. And, furthermore, always carry a small snake.

Pingu

  • Put away those fiery biscuits!
  • Mrs Pingu's domestique
    • the Igloo
Re: =SUM(A23/B21)
« Reply #81 on: 08 February, 2019, 09:56:27 am »
I came across my first example of the unnecessary use of the SUM() function in Excel in the wild this week.

FifeingEejit

  • Not Small
Re: =SUM(A23/B21)
« Reply #82 on: 08 February, 2019, 01:15:26 pm »
Anyway I got to reading through the code and it essentially ran a simple query against the trading database and then did endless processing of the results, copying them to one worksheet after another. It turned out the trader who wrote it didn't know that SQL had a handy GROUP BY clause, so had written it himself using huge amounts of inefficient code. And because he wasn't used to writing robust code he hadn't bothered to do little things like check whether a trading book existed before downloading it.

They probably went on to write a Document database with a mildy offensive name and delight in the fact there's No-SQL.
Or maybe not.

Most times someone comes out with "No-SQL" I remind them of the "No-SQL" Database that came with Windows 3.1
It was called Card file.

Their education in Set maths then starts with "This was in the SPMG Primary 3 Maths book"