Microsoft Adds Support for JavaScript Functions in Excel
-
@heterodox said in Microsoft Adds Support for JavaScript Functions in Excel:
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
I'm pretty sure they are doing, somewhere (having hardware support for your fractional number system helps a lot with performance) but they might've wrapped them up inside objects.
Am I the only one here who's optimistic and thinks they could be using an arbitrary precision library?
- Write "1" in A1.
- Write "0.9" in A2.
- Select both.
- Drag-fill the next 15 rows.
- Look at A11.
-
@onyx said in Microsoft Adds Support for JavaScript Functions in Excel:
The result was "phone numbers" in the form of 9.12E+17, which you could expand, yes, but due to loss of precision the second number was unsalvageable, even manually, because loss of precision ate the last few digits
This used to happen all the time when I was importing spreadsheets of billing data that had been sent in CSV and then manually saved as xls. The best one was where not only had phone numbers been mangled but any actual numeric amounts had been destroyed beyond all realistic ability to salvage them by a difference in locale settings and specifically whether
,
was a decimal or thousands separator.
-
@gąska said in Microsoft Adds Support for JavaScript Functions in Excel:
@heterodox said in Microsoft Adds Support for JavaScript Functions in Excel:
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
I'm pretty sure they are doing, somewhere (having hardware support for your fractional number system helps a lot with performance) but they might've wrapped them up inside objects.
Am I the only one here who's optimistic and thinks they could be using an arbitrary precision library?
- Write "1" in A1.
- Write "0.9" in A2.
- Select both.
- Drag-fill the next 15 rows.
- Look at A11.
Now what?
-
@jaloopa I think it means it's smart enough to not be 0.00000000003652
-
@jaloopa huh, they fixed it.
-
This thread needs more "proper in its title!
-
@heterodox said in Microsoft Adds Support for JavaScript Functions in Excel:
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
I'm pretty sure they are doing, somewhere (having hardware support for your fractional number system helps a lot with performance) but they might've wrapped them up inside objects.
Am I the only one here who's optimistic and thinks they could be using an arbitrary precision library?
Am I the only one here who habitually wraps
ROUND( ... ,2)
around currency calculations because I've personally discovered that they aren't using an arbitrary precision library?
-
edit: and like my previous post said, putting the calculation inside
ROUND( ... ,2)
makes it produce the expected result.
-
@anotherusername fun fact: Excel uses locale data to determine what the decimal separator and the argument separator are. I stared at the second image in your post trying to figure out how the fuck has Excel rounded 0.11 to 0.09.
-
@gąska yes, replace
,
with;
and.
with,
if applicable by your regional settings...
-
@anotherusername yeah, that one's easy. Much harder is the fact that Excel translates every single function name so you can't just copy-paste English formulas into non-English Excel. Thankfully, there's a dictionary.
-
@anotherusername said in Microsoft Adds Support for JavaScript Functions in Excel:
@heterodox said in Microsoft Adds Support for JavaScript Functions in Excel:
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
I'm pretty sure they are doing, somewhere (having hardware support for your fractional number system helps a lot with performance) but they might've wrapped them up inside objects.
Am I the only one here who's optimistic and thinks they could be using an arbitrary precision library?
Am I the only one here who habitually wraps
ROUND( ... ,2)
around currency calculations because I've personally discovered that they aren't using an arbitrary precision library?Much more insidious for newcomers is the difference between "display rounding" and "actual rounding". Excel will happily display "4" by default when the underlying number is actually "3.98"
That's always fun to explain to my pupils.
-
@rhywden sure, and it depends on the width of the column it's in.
-
@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:
basically like a "command history" kind of
I'm imagining the horror of Excel trying to implement a cell history mechanism now....
-
@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:
how about multiple statements and variables?)
It's an expression, there's no such things as "multiple statements". Do you even Excel bro?
-
@tsaukpaetra said in Microsoft Adds Support for JavaScript Functions in Excel:
@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:
basically like a "command history" kind of
I'm imagining the horror of Excel trying to implement a cell history mechanism now....
I'm sure there's an SVN or git client in NPM somewhere...
-
@mott555 said in Microsoft Adds Support for JavaScript Functions in Excel:
@tsaukpaetra said in Microsoft Adds Support for JavaScript Functions in Excel:
@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:
basically like a "command history" kind of
I'm imagining the horror of Excel trying to implement a cell history mechanism now....
I'm sure there's an SVN or git client in NPM somewhere...
Stop it! I have half the spec getting auto-drafted in my head now!
-
@tsaukpaetra said in Microsoft Adds Support for JavaScript Functions in Excel:
It's an expression, there's no such things as "multiple statements". Do you even Excel bro?
They could totally add the equivalent of
WITH someValue AS () ... SELECT * FROM someValue
from postgresql.
-
@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:
@tsaukpaetra said in Microsoft Adds Support for JavaScript Functions in Excel:
It's an expression, there's no such things as "multiple statements". Do you even Excel bro?
They could totally add the equivalent of
WITH someValue AS () ... SELECT * FROM someValue
from postgresql.
-
@boomzilla said in Microsoft Adds Support for JavaScript Functions in Excel:
@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:
here's another thing - how about multiple statements and variables?
Uh, that's what cells are, dude.
if A2 goto B2
-
@dreikin Excuse me, I have to kill someone now. I'm not sure who, but the Voices are telling me that a blood sacrifice is required to atone for that eldritch horror.
-
@dreikin said in Microsoft Adds Support for JavaScript Functions in Excel:
@boomzilla said in Microsoft Adds Support for JavaScript Functions in Excel:
@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:
here's another thing - how about multiple statements and variables?
Uh, that's what cells are, dude.
if A2 goto B2
You're getting surprisingly close to re-inventing LabVIEW.
-
@hardwaregeek said in Microsoft Adds Support for JavaScript Functions in Excel:
@dreikin Excuse me, I have to kill someone now. I'm not sure who, but the Voices are telling me that a blood sacrifice is required to atone for that eldritch horror.
A B 1 Sacrifice Required? True 2 Victims Jeff 3 Boomzilla 4 =GOTO B2 IF B1 ELSE GOTO B3
-
@dreikin said in Microsoft Adds Support for JavaScript Functions in Excel:
@boomzilla said in Microsoft Adds Support for JavaScript Functions in Excel:
@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:
here's another thing - how about multiple statements and variables?
Uh, that's what cells are, dude.
if A2 goto B2
You kid, but I wrote an interpreter system that indeed knew how to alter the sequence of execution...
Too bad it was wrapped up in IP and I couldn't take it with me from $employer...
-
@dreikin said in Microsoft Adds Support for JavaScript Functions in Excel:
@hardwaregeek said in Microsoft Adds Support for JavaScript Functions in Excel:
@dreikin Excuse me, I have to kill someone now. I'm not sure who, but the Voices are telling me that a blood sacrifice is required to atone for that eldritch horror.
A B 1 Sacrifice Required? True 2 Victims Jeff 3 Boomzilla 4 =GOTO B2 IF B1 ELSE GOTO B3
-
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
@steve_the_cynic said in Microsoft Adds Support for JavaScript Functions in Excel:
Possibly not, or at least not in the file format.
The XLSX would… except the pure numbers there are almost certainly human-readable text (inside XML inside a ZIP).
The flaw in that reasoning is that Excel 2016 is still able to read antique .XLS files that predate even the existence of XML, much less Microsoft's use of it for Office file formats. Which returns to the original questions: what floating point format did 68K Macs use? What did Excel write in saved spreadsheets on a 68K Mac? What about Excel for Windows-2.1? (Yes, that was a thing. Well, both Windows 2.1 AND Excel for Windows-2.1 were things. I used both (at the same time) in 1989.)
-
@steve_the_cynic said in Microsoft Adds Support for JavaScript Functions in Excel:
Well, both Windows 2.1 AND Excel for Windows-2.1 were things. I used both (at the same time) in 1989.
And you didn't run out of memory?
-
@steve_the_cynic said in Microsoft Adds Support for JavaScript Functions in Excel:
Which returns to the original questions: what floating point format did 68K Macs use?
A lot of them didn't even have a FPU. The 68LC040 was a lot cheaper. (The "LC" actually stood for "low cost". Motorola wasn't good at product naming.)
-
@blakeyrat said in Microsoft Adds Support for JavaScript Functions in Excel:
The 68LC040 was a lot cheaper. (The "LC" actually stood for "low cost". Motorola wasn't good at
product naminglying to customersmarketing.)FTFY.
-
@blakeyrat said in Microsoft Adds Support for JavaScript Functions in Excel:
The "LC" actually stood for "low cost". Motorola wasn't good at product naming.
A project I worked on a few years ago had the letters "TM" in the product name, where "TM" meant "Time to Market." Meaning "Get this stinking, looooong-overdue product out the door even if some of the features don't work; then we'll come back and fix the broken bits as a new product."
-
@heterodox said in Microsoft Adds Support for JavaScript Functions in Excel:
Am I the only one here who's optimistic and thinks they could be using an arbitrary precision library?
They support more than just basic elementary arithmetic, so no, they're not. Libraries for that sort of thing exist, but they're really uncommon and quite slow as the mathematics behind them is… non-trivial. Obvious things that you might try tend to have awful edge cases relatively commonly (e.g., continuing fractions have difficulty deciding what the first digit of (√2)2 is, unless you allow the generators you convert them into to rescind decisions taken earlier, which is awful from a programming perspective) and the whole area is deceptively tricky. The best solutions I've seen are sufficiently slow that you'd only ever use them rarely, to check that other less-accurate-but-orders-of-magnitude-faster code is doing the right thing in relation to numerical stability and such. Sticking to elementary arithmetic would let you use just rational numbers internally, but that closes off a lot of fairly common operations.
So no, Excel isn't using arbitrary precision arithmetic internally (except perhaps for integers, which are a lot less unruly). That's totally the right decision for MS to have taken.
-
@hardwaregeek said in Microsoft Adds Support for JavaScript Functions in Excel:
A project I worked on a few years ago had the letters "TM" in the product name, where "TM" meant "Time to Market." Meaning "Get this stinking, looooong-overdue product out the door even if some of the features don't work; then we'll come back and fix the broken bits as a new product."
Back when I was a freelance roleplaying game writer, I can remember one book that was given a working title along the lines of “Boo Hiss Scary Shadowrun Book of the Night.” This to make sure that title definitely wouldn’t end up on the cover of the finished product, since working titles had a tendency of becoming final. (In the end it didn’t matter because the book was never published, but hey.)
-
@gurth the working copy of my database design group project in college, had the title page literally saying "<PROJECT TITLE>" (without quotes, but with angle brackets). We didn't bother to change it, and only noticed when we were handing it over.
-
@gąska said in Microsoft Adds Support for JavaScript Functions in Excel:
@steve_the_cynic said in Microsoft Adds Support for JavaScript Functions in Excel:
Well, both Windows 2.1 AND Excel for Windows-2.1 were things. I used both (at the same time) in 1989.
And you didn't run out of memory?
386 with expanded memory (the mighty EMM386.SYS was a thing, just). The machine had, if memory serves, 2MB of RAM in total and Excel used the expanded memory to store stuff.
It was also a bit smaller than Excel 2016.
-
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
they're really uncommon and quite slow as the mathematics behind them is… non-trivial
I ought to clarify here. The best option I've seen so far involved way more tensors than are healthy and was implemented using complex multi-layer generators and a large number of magic constants. I went as far as working through some of the simpler cases to figure out that yes, they'd do what was claimed, but boy, did that code scare me.
-
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
So no, Excel isn't using arbitrary precision arithmetic internally (except perhaps for integers, which are a lot less unruly).
Wat? All you need for exact calculations with integers is a large enough integer type.
-
@anotherusername said in Microsoft Adds Support for JavaScript Functions in Excel:
All you need for exact calculations with integers is a large enough integer type.
Yes. But that's probably not what was done. And Excel definitely isn't able to be implemented with just integers and rationals; it supports logarithms and trig functions and so on, and those simply don't work with simple finite fraction approach. However, I wouldn't be nearly so surprised if Excel these days internally used decimal floating point, at least while only elementary functions are in use. It's fairly rare as it isn't as efficient as binary floating point, but has its substantial advantages given that a lot of Excel use cases involve money. There's even an IEEE specification for decimal FP, though for ages the only notable implementation was by IBM.
I'd still rate that unlikely, given how much work it would take and the general state of programmers these days. After all, they've gone and added JS instead…
-
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
way more tensors than are healthy
Insert joke about tensors causing hypertension, or something like that.
As much math as I had to take for an Engineering degree, none even touched on WTF a tensor is. And I've never been sufficiently motivated to try to learn on my own.
-
@hardwaregeek said in Microsoft Adds Support for JavaScript Functions in Excel:
WTF a tensor is
Higher order matrices.
-
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
Yes. But that's probably not what was done.
Natch.
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
I wouldn't be nearly so surprised if Excel these days internally used decimal floating point, at least while only elementary functions are in use. It's fairly rare as it isn't as efficient as binary floating point, but has its substantial advantages given that a lot of Excel use cases involve money.
Hahaha... no. That'd make way too much sense. Just throw a double at it... that should be good enough, right?
-
@gąska in my Career Exploration class this semester, my group accidentally named itself Group Insert Group Name Here, and we've just been rolling with it the whole class.
-
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
integers and rationals;
Your definition of "rationals" is a bit wonky. The integers themselves are rationals.
The "proper" definition of "rational number" is any number that can be expressed as a fraction (or "ratio" - "ratio" ==> "ratio-nal") of two integers. 47 is 47/1 (or 94/2, etc.), so 47 is a rational number.
-
Javascript Functions in Excel is en excellent idea! Now I can finally use left-pad to correctly align my cells.
-
@steve_the_cynic Mathematically, rationals are indeed a superset of integers (which are a superset of natural numbers). Computationally though, rationals are pairs of integers, and most people working with them seem to like to use infinite-width integers for the numerator and denominator.
-
@steve_the_cynic said in Microsoft Adds Support for JavaScript Functions in Excel:
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
integers and rationals;
Your definition of "rationals" is a bit wonky. The integers themselves are rationals.
There's nothing wrong with his definition. Any integer can be expressed as the fraction between itself and the integer 1.
-
@anotherusername said in Microsoft Adds Support for JavaScript Functions in Excel:
@steve_the_cynic said in Microsoft Adds Support for JavaScript Functions in Excel:
@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:
integers and rationals;
Your definition of "rationals" is a bit wonky. The integers themselves are rationals.
There's nothing wrong with his definition. Any integer can be expressed as the fraction between itself and the integer 1.
for sure, but unless we permit the definition to include "1 is between 0 and 2" (between two arbitrary integers), it's a bit bogus. The statement made would contextually be interpreted as "two arbitrary consecutive integers"... But indeed that isn't necessary - a rational is simply a ratio of integers with a non-zero denominator. That is all the definition you need.
-
@blakeyrat said in Microsoft Adds Support for JavaScript Functions in Excel:
@steve_the_cynic said in Microsoft Adds Support for JavaScript Functions in Excel:
Which returns to the original questions: what floating point format did 68K Macs use?
A lot of them didn't even have a FPU.
Sure, but they were still capable of doing floating point arithmetic. What format did they use? IEEE? IBM System/360?(1) The 48-bit format from the Nord 100? Something else?
(1) A demonic FP system using hexadecimal rather than binary. Still mantissa/exponent, but the base for the exponent was 16, not 2.
-
@steve_the_cynic said in Microsoft Adds Support for JavaScript Functions in Excel:
for sure, but unless we permit the definition to include "1 is between 0 and 2" (between two arbitrary integers), it's a bit bogus. The statement made would contextually be interpreted as "two arbitrary consecutive integers"... But indeed that isn't necessary - a rational is simply a ratio of integers with a non-zero denominator. That is all the definition you need.
Wat. No seriously, where from "arbitrary fractions between two arbitrary integers" do you get that they must be two consecutive integers? Arbitrary means the opposite of that.
Any value which is mathematically equal to some fraction a/b where a and b are integers is rational. Integers fit this definition, because a=a/1.
You're right that the denominator cannot be 0. But if it is, the fraction cannot be evaluated; it would make no sense to say that the result of the fraction's evaluation is a rational number, because the ratio is infinite. Infinity is not a number.
-
@anotherusername said in Microsoft Adds Support for JavaScript Functions in Excel:
Infinity is not a number.
Ah, but
Inf != NaN
-
@dkf I mean mathematically.
If we're picking on Javascript,
isNaN(Infinity)
returnsfalse
...