Floating point, Units & Decimal places
The next goal I’m working towards is being able to do my own retirement calculations.
Towards that goal, I added a bunch of basic math functionality this week:
- Floating point numbers now work and integer/floating point interactions work as expected.
- Numbers can now have _ in them, to allow for easier reading, eg: 1_000_000 vs 1000000
- Negative numbers!
Adding _ as a valid character in numbers is an interesting one. It's a feature in python, C#, a bunch of programming languages and makes numbers much more readable. Commas don't work in numbers in programming languages that use commas as a delimiter in function calls.
What's next part 1: Units!
I think it'd be great to say in a spreadsheet 30mi / 5hr
and get back a result of 6 mph
. That implies that once you've added a unit to one cell, that unit can keep carrying down to later cells it's used in, so you only really have to specify it in your originating constants.
You'll also need the ability to do conversions - eg 3m in ft
and so forth, which seems like a win.
Besides auto-conversions and smart-ness around units, there's a big error-prevention win in ensuring that conflicting units are caught, eg. if you're trying to add a variable in feet to a variable in meters, don't just add the numbers - convert to a common unit, then add and display the unit of the result (eg. 30ft + 60m
results in 226ft
, not 90
)
I'm pretty excited about this. Hope to have basics working next week.
What’s next part 2: Decimal places!
NOTE: This section is definitely a "thinking out loud" exercise. We're going to go down a bit of a rabbit hole, and I am less certain about this than I am about the other work I've done so far.
Once I added floating number numbers I realized that the lack of any support of decimal place display control was going to be a pain. Dogfooding is going to be pretty annoying if every time I calculate interest, I end up with long numbers like 47,540.85723430.
So can I be smarter about that? Can I figure out what you want to see automatically?
This is an area with a lot of contradictory requirements from different users/use cases.
- Physical sciences - they have a bunch of significant figure rules that convey uncertainty of measurements. eg. http://www.pstcc.edu/nbs/WebPhysics/significant_figures.html
- Currency - all digits are significant
- Financial/retirement planning - sometimes you just don't want to worry about the details in currency. In my retirement planning I just don't want bother with values below $1,000
- "I just really don't ever actually want to see 7 decimal places in any math" - this is where I feel that I land.
.... so this has is setting off all of my "this is a hard problem that maybe has a perfect solution that magically works for all users, but... probably not". I can see why spreadsheets don't try to be smart about this.
But.... I do think maybe we can improve on the existing spreadsheet and choose a smarter default.
Units to the rescue
When I'm doing retirement calculations, I'm often really just interested in thousands of dollars, not hundreds, and really don't bother me with the cents. $375,463.27
is just overkill. $375,000
is much more helpful.
I think units may offer a way out - it's common to use notation like $375k
or $1.5M
, and it's also pretty clear when using that notation that you don't care about more than one place after the decimal.
This implies something interesting: using SI units like k or cm is a good way to indicate the scale of what you're interested in. So let's try to just "do the right thing" by letting users indicate the scale they want to see by either units or number of decimal places in the number, and then only show 1 decimal place by default. They can always change the unit of what's being displayed, or just directly change the "how many decimal places to show" setting, which will get inherited and thus "do the right thing" for later work.
What exactly am I proposing?
NOTE: This is going to seem complicated. My hypothesis is that when users start using it, it'll "just work", but as I said above, this is a thinking out loud exercise and definitely could be wrong/more complicated than I think.
- Default to only ever showing 1 decimal place
- Base currency ($/€) will default to 2 decimal places, but "financial amount" currency units (like
$375k
) will only show 1 decimal place
- Base currency ($/€) will default to 2 decimal places, but "financial amount" currency units (like
- Users can change the number of decimal places shown:
- On a unit level across the whole worksheet: eg. a user could change a setting to say "all
m
unit values should show 12 decimal places" - On a per cell level (this will be inherited)
- If a user manually types in a number with decimal places, then that number of decimal places will be inherited to child cells
- On a unit level across the whole worksheet: eg. a user could change a setting to say "all
- Number of decimal places shown when two inherited values conflict:
- User's explicit input will take precedent
- Otherwise the unit of the result will be used
- Unless a user explicitly rounds a number using the round function (eg.
ROUND(347m/42)
), the underlying value stored by the system is the full unrounded number- ... except for currency, where I believe the standard is to always round to cents. (need to do more research on that, and not sure what the story is for other non-USD currencies)
- Use
...
at the end of a number to indicate that there are more digits potentially visible. (eg.36.9...
)
What do y'all think? Am I trying to be too smart by defaulting to showing fewer decimal places? Should I make it consistently 2 decimal places across the board then currency/everything else matches up?
My next step is to do a few mock ups of types and this decimal places behavior, hopefully that will make it clear whether this is actually simpler or not :)