Blog: Tech Talk

agsdix-fas fa-home

Blog: Home

agsdix-fas fa-pen-fancy

Blog: CEO's Corner

agsdix-fas fa-code

Blog: Tech Talk

Blog: Product Releases

agsdix-fas fa-chalkboard-teacher

Blog: Viewing

Blog: Conversion

arrow left circle icon Blog: Tech Talk

Understanding Excel Custom Number Formats: Part 2

by | Oct 12, 2020

Order of Operators

In the first part, I talked about some basic features and quirks of Excel’s custom number format. This time, I’ll talk more in depth about how Custom Formats order glyphs it receives. Sometimes, it’s straightforward, but sometimes the order doesn’t matter. This ended up being the most confusing and frustrating thing to get right while developing this feature for RasterMaster, so I’ll do my best to be precise in writing about it.

Whole and Decimal

To the best of my knowledge, Custom Number formats treat whole number results different from decimal number results, in a way that is fairly “human intuitive.” They’re things that make sense, but end up encompassing a number of exceptions and edge cases. First and most obviously, the 0 source character will always put a glyph in the result. So what happens when your format has more 0s than written numbers? More are added!

written number --> format --> result

12345 --> 0000000000 --> 0000012345

The written number is five charaters long, but there are ten source 0s, so the result has all five written numbers and five additional glyphs, all added onto the leftmost end of the result. Obviously, if you put the zeroes toward the right of the number, the result would unexpectedly alter the the size of the written number.

So what would happen to a decimal number that has extra source characters?

.12345 --> .0000000000 --> .1234500000

Unsurprisingly, the same expected result occurs. But for the number to not change the 0s need to be added onto the rightmost end of the result.

Now, folks who read the first part may remember that ,s can actually change the result of the source number. How does this interact with filling the results with additional source characters?

12345 --> 0000000000,, --> 0000000000

.12345 --> .0000000000,, --> .0000001235

Ultimately, I concluded that ,s divide the written number by 1,000 before attempting to do any formatting on the number. It’s part of a system I called “Modifiers”, which alter the written number or format before doing any formatting. There are two Modifiers that are special and exist outside of other Modifiers. They are , and %. The % modifier multiplies the number by 100 before any formatting. I’ll go through the other types of Modifiers in another part.

12345 --> %00000%00000% --> %123450%00000%

.12345 --> %.%00000%00000 --> %123450.%00000%00000

Whoa, that gave a pretty offbeat result. First thing to notice, the written decimal number is no longer a decimal. Yep, that’s the thing about custom formats: you can’t not show whole numbers. You can make written decimal numbers disappear, but you’re stuck with the written whole number. Second, unlike the ,s, the % glyphs didn’t disappear, but remained in the number. Anywhere they are placed they’ll stay, and any amount in the format will continue to multiply the written number by 100. Finally, pay special attention to where the percentage signs were placed, because this is where things get complicated.

In the decimal format, I placed a % at the leftmost position, leftmost right of the deicmal point, and in the middle of the source characters. Why are they so spread out in the result?

The explanation is a double edge case of formatting: the leftmost and rightmost positions on each side of the decimal in the format are special. Anything in between is a counted position in a list. Here is a smaller format that better explains the counted position.

1 --> %????%? --> %1000%0

The format calls for two %s and five ?s. Two % multiply the number to make it become 10,000 before formatting, filling all the space available. However, there is a leftmost % and in the second position. The leftmost % will always be on the left, and the other % will always be second from the rightmost, no matter how large the number gets. Here are two numbers formatted by the same format, without any Modifiers, but with more characters added by the format.

987654321 --> " z "#,#" d "#" c "#" b "#" a "0 --> z 987,65 d 4, c 3 b 2 a 1

321 --> " z "#,#" d "#" c "#" b "# a "0 --> z d c 3 b 2 a 1

The z is always on the rightmost edge of the number, no matter how large the number. The other letters slot themselves in between available result characters, starting count from the leftmost. Also notice how ,s were added to the larger number, but inserted next to the result numbers. It means that string characters added by the format only care about source characters and other strings.

As the last example, what happens when you apply this kind of formatting to a decimal number?

.123 --> .0" a "#" b "#" c "#" d "#" z" --> .1 a 2 b 3 c d z

The same quirky ordering system occurs, but now in reverse, where string characters attempt to slot themselves in starting at the leftmost character.

Takeways

  • Whole and decimal are not the same and sometimes need to be treated as mirrors of each other.
  • The leftmost and rightmost position of whole and decimal formats are special and results can’t place characters beyond those spots.
  • If a result would generate characters that cross over the decimal point, those characters are subject to the rules of the “side” they fall on rather than where they are in the written number.
  • A format can result in both whole and decimal results even if the written number or format don’t make account for one or the other.
  • Each source character added to a format increases the “size” of a list in which new characters can be placed in between.
  • Any part of the format that is not a source character needs to play nice with all the source characters.

Operations of Order

This was the most difficult thing for me to understand while working on custom formats, and it took a lot of guessing, refactoring, and experimentation come to this. But ultimately it was worth it, for this small sliver of custom number formatting is actually more robust in RasterMaster than Excel. I can’t say the same for other portions, like in the next article, which will deal with fractions, and scientific notation.

Tyler Brogna currently works at Snowbound as an Engineer on RasterMaster. He attended Rochester Institute of Technology and graduated with a BS in Game Design and Development. He collects video games and sometimes designs them as well.

Action Items: