Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

3D engine entirely made of MS Excel formulae

gamasutra.com

Post reply on HN
Print view View original

71–78 of 78 posts

Re: 3D engine entirely made of MS Excel formulae

#71

Post by zokier »

eggy wrote: post #54
chris_wot wrote: post #35

Earlier quoted context omitted.

Given the problems with statistical functions in Excel, perhaps we should be a tad worried?

A lot of Excel's issues are well-known because of how pervasive it is. Python and kin rely on C libraries and other layers of imported packages for numerical work that can lead to all sorts of numerical mischief. Even doing: round (2.575, 2) in Python leads to 2.57 instead of rounding up to 2.58. Everything in our engineering firm is checked by another engineer.

    In [7]: round(2.575, 2)
    Out[7]: 2.58
Umm?

Re: 3D engine entirely made of MS Excel formulae

#72

Post by EForEndeavour »

zokier wrote: post #71
eggy wrote: post #54

Earlier quoted context omitted.

A lot of Excel's issues are well-known because of how pervasive it is. Python and kin rely on C libraries and other layers of imported packages for numerical work that can lead to all sorts of numerical mischief. Even doing: round (2.575, 2) in Python leads to 2.57 instead of rounding up to 2.58. Everything in our engineering firm is checked by another engineer.

In [7]: round(2.575, 2) Out[7]: 2.58 Umm?

From the Python 3.7 docs (https://docs.python.org/3/library/functions.html#round):

The behavior of round() for floats can be surprising: for example, round(2.675, 2) gives 2.67 instead of the expected 2.68. This is not a bug: it’s a result of the fact that most decimal fractions can’t be represented exactly as a float.

Re: 3D engine entirely made of MS Excel formulae

#73

Post by eggy »

rejschaap wrote: post #64
eggy wrote: post #56

Earlier quoted context omitted.

I love PowerShell for this reason. I had a 100 CSV files, and I needed the first two rows removed to put them into a signal viewing app, and it took only a few lines of PowerShell to do this on my corporate machine. I would normally do this in another PL, but strangely PowerShell's solution further pushed me to look into it for other uses on that machine. I wish there were a good book for this, since I actually prefe…

I work in a Microsoft shop, but I always have a bash shell running. It prevents me from really diving into PowerShell. I will end up running a quick sed '2,3d' on the csv files and move on with my life.

I have PowerShell on my local company machine, and without Admin priveleges I can't even sideload bash through a Git install. We're not a software company, so requesting Git is also out of the standard operational procedures as a user. IT knows my coding background (since 1978), but they can't change policy.

Re: 3D engine entirely made of MS Excel formulae

#74

Post by dan-robertson »

aaronblohowiak wrote: post #44
dan-robertson wrote: post #38

Earlier quoted context omitted.

I think I disagree entirely. The typical things one gains from higher order functions are things like mapping and filtering of data structures. Excel only has one data structure—the table. Mapping is done by writing the formula once and then dragging it from the corner to the whole column. Filtering is not really done. Normally use the gui to hide the rows to be filtered away. Functions add in questions of scoping. H…

Filtering is very much done in sheets I have seen and used

Yes but it’s not done with a function like filter in scheme or Haskell, it’s done by the gui, outside of the world of formulas. For more complex stuff there are database features hidden away inside

Re: 3D engine entirely made of MS Excel formulae

#75

Post by zokier »

EForEndeavour wrote: post #72
zokier wrote: post #71

Earlier quoted context omitted.

In [7]: round(2.575, 2) Out[7]: 2.58 Umm?

From the Python 3.7 docs ( https://docs.python.org/3/library/functions.html#round ): The behavior of round() for floats can be surprising: for example, round(2.675, 2) gives 2.67 instead of the expected 2.68 . This is not a bug: it’s a result of the fact that most decimal fractions can’t be represented exactly as a float.

Well that is just normal float fun :)

    In [17]: Decimal(2.675)
    Out[17]: Decimal('2.67499999999999982236431605997495353221893310546875')
So sort of round() is working as expected, but the number you are not inputting is not the one you are expecting.

Re: 3D engine entirely made of MS Excel formulae

#76

Post by oldandtired »

rightbyte wrote: post #69
oldandtired wrote: post #66

Earlier quoted context omitted.

That is certainly something that I have seen as well. The problem lies in validating the spreadsheets. Often, the amount of validation is minimal, whether that is the actual formulae used or the relationships that bare created or even the data that is used. I have seen some outstanding examples of well written spreadsheets but these tend to be the minority case. Depending on the organisation and the priority it puts…

Is validation a problem? You see the intermediate steps of calculation in the columns. It's way more clear then black-box unit testing. It's like some nice live debugging with print statements everywhere.

Validation is always a problem. In one early example, I came across, the core data on which the model did not match what was really there. The data was assumed to be correct and, of course, nobody actually did the checking. The previous year's data was used for the following year's calculations.

I knew the data for the spreadsheet was wrong only because I had been involved in looking at the actual data sources in weeks previously.

But there can also be formulae errors. They seem to produce the correct information, but they will have subtle errors that miss necessary edge cases or use set values when they shouldn't.

Re: 3D engine entirely made of MS Excel formulae

#77

Post by oldandtired »

rightbyte wrote: post #68
oldandtired wrote: post #52

Excel has been an interesting tool that has been abused by many different people for many different subject areas, from petro-engineers to asset management to budget forecasting to drug sales forecasting to activity planning and on and on and on. With a judicious use of vba, many of the tasks above, especially those related to forecasting, planning and reporting can be automated down from hours to just seconds. I hav…

The problem is that when you involve a Real Programmer you ruin the flexibility of the sheet that made it so useful for the original user. It's better waste hours using Excel than waste even more hours trying to convince some developer to do a change and wait for the product - which might never come.

I have been the "Real Programmer" that was closely involved with building the sheet models. The flexibility is in being able to build lots of different models for investigation. It is oft found that the model builder misses some of the subtleties of using spreadsheets and their models don't quite go the way they expect.

In my case, I was on call to discuss what was to be achieved and to help them build what they needed. The turn around times were short. My function as the "Real Programmer" was to get them into a position of solving any relevant problems and making sure that they were able to progress with their work. That is the beauty of working directly for the end-user instead of being part of the central IT team.

Re: 3D engine entirely made of MS Excel formulae

#78

Post by chris_wot »

slacka wrote: post #46
chris_wot wrote: post #36

I should open this in LibreOffice :-) Actually, I’ve been wondering how to create a comprehensive spreadsheet that tests all of Excel’s features. Kind of like an ACID3 for spreadsheets...

Yes it actually works quite well as long as you use the 1st, macro version. If you have LibreOffice installed, it takes less time to actually try it, than to post a comment.

A bit snarky of you, given I didn't actually ask a question. Did you notice the bit where I mentioned an ACID3 style document?

I'm in the commit logs of LibreOffice, incidentally.

Post reply on HN