Using HTML5 Fractions in Exported Excel Tables

HTML5 provides for fractional representation of halves, thirds, fourths, fifths, sixths, no sevenths, and eighths. Excel has a fractional number format. This post is about bringing these concepts together for exporting an Excel table into Wiki or HTML designs. The basic representations are:

Name Hex Dec Result
½ U+000BD 189 ½
⅓ U+02153 8531
¼ U+000BC 188 ¼
⅕ U+02155 8533
⅙ U+02159 8537
⅛ U+0215B 8539
⅔ U+02154 8532
⅖ U+02156 8534
¾ U+000BE 190 ¾
⅗ U+02157 8535
⅜ U+0215C 8540
⅘ U+02158 8536
⅚ U+0215A 8538
⅝ U+0215D 8541
⅞ U+0215E 8542

 
The format for the name is &fracnd; where n is the numerator and d is the denominator. Thus ½ is a half, and ⅞ is seven-eighths. The HTML code representations for these are:

Result Named
Code
Hex
Code
Dec
Code
½ ½ ½ ½
⅓ ⅓ ⅓
¼ ¼ ¼ ¼
⅕ ⅕ ⅕
⅙ ⅙ ⅙
⅛ ⅛ ⅛
⅔ ⅔ ⅔
⅖ ⅖ ⅖
¾ ¾ ¾ ¾
⅗ ⅗ ⅗
⅜ ⅜ ⅜
⅘ ⅘ ⅘
⅚ ⅚ ⅚
⅝ ⅝ ⅝
⅞ ⅞ ⅞

 
In theory (more on “in practice” later) every representation in a row is equivalent. This is our test table to export to Wiki or HTML format:

D E F G H I J K L
1 1/1 1/2 1/3 1/4 1/5 1/6 1/7 1/8 1/80
2 2/1 2/2 2/3 2/4 2/5 2/6 2/7 2/8 2/8.
3 3/1 3/2 3/3 3/4 3/5 3/6 3/7 3/8 3/8A
4 4/1 4/2 4/3 4/4 4/5 4/6 4/7 4/8 6 4/8
5 5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 7 5/8
6 6/1 6/2 6/3 6/4 6/5 6/6 6/7 6/8 8 6/8
7 7/1 7/2 7/3 7/4 7/5 7/6 7/7 7/8 9 7/8
8 8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

 
Cell D1: =CHAR(32)&ROW()&”/”&COLUMN()-3, then fill down and right. The right hand column has a few test cases. I used that formulaic construction to keep Excel from doing the divisions. The Excel fractional format is “# ?/?” for single digit denominators. The intermediary space is important. It indicates that a fraction may follow, just as a forward slash indicates a fraction may be present. And a format of /?? is a fraction not translatable into HTML5. Turning these patterns into VBA, this is my MakeFracs() function. It checks that there is a slash, then that there is not a slash–digit–digit pattern, and finally that there is a “space–digits 1 through 7–slash–digits 2, 3, 4, 5, 6, 8” pattern to screen out sevenths and ninths. If all of those pass, it substitutes in the &fracnd; formulation for the fraction.

Function MakeFracs(Arg As String) As String
Dim sIN As String
Dim sOUT As String
Dim i As Long, j As Long
Dim n As Long, d As Long
Dim Fracs(1 To 15, 1 To 3) As String

Fracs(1, 1) = "½": Fracs(1, 2) = "½": Fracs(1, 3) = "½"
Fracs(2, 1) = "⅓": Fracs(2, 2) = "⅓": Fracs(2, 3) = "⅓"
Fracs(3, 1) = "¼": Fracs(3, 2) = "¼": Fracs(3, 3) = "¼"
Fracs(4, 1) = "⅕": Fracs(4, 2) = "⅕": Fracs(4, 3) = "⅕"
Fracs(5, 1) = "⅙": Fracs(5, 2) = "⅙": Fracs(5, 3) = "⅙"
Fracs(6, 1) = "⅛": Fracs(6, 2) = "⅛": Fracs(6, 3) = "⅛"
Fracs(7, 1) = "⅔": Fracs(7, 2) = "⅔": Fracs(7, 3) = "⅔"
Fracs(8, 1) = "⅖": Fracs(8, 2) = "⅖": Fracs(8, 3) = "⅖"
Fracs(9, 1) = "¾": Fracs(9, 2) = "¾": Fracs(9, 3) = "¾"
Fracs(10, 1) = "⅗": Fracs(10, 2) = "⅗": Fracs(10, 3) = "⅗"
Fracs(11, 1) = "⅜": Fracs(11, 2) = "⅜": Fracs(11, 3) = "⅜"
Fracs(12, 1) = "⅘": Fracs(12, 2) = "⅘": Fracs(12, 3) = "⅘"
Fracs(13, 1) = "⅚": Fracs(13, 2) = "⅚": Fracs(13, 3) = "⅚"
Fracs(14, 1) = "⅝": Fracs(14, 2) = "⅝": Fracs(14, 3) = "⅝"
Fracs(15, 1) = "⅞": Fracs(15, 2) = "⅞": Fracs(15, 3) = "⅞"

i = VBA.InStr(1, Arg, "/", vbTextCompare)
If i = 0 Then 'there's no fraction
MakeFracs = Arg
ElseIf Mid$(Arg, i, 3) Like "/##" Then 'not HTML5
MakeFracs = Arg
ElseIf Mid$(Arg, i - 2, 4) Like " [1-7]/[234568]" Then
sOUT = Mid$(Arg, i - 1, 3)
n = VBA.Val(Left$(sOUT, 1)) 'numerator
d = VBA.Val(Right$(sOUT, 1)) 'denominator
If n < d Then If d Mod n = 0 Then d = d / n n = 1 ElseIf d Mod 2 = 0 And n Mod 2 = 0 Then d = d / 2 n = n / 2 End If sIN = "&frac" & n & d & ";" For j = 1 To 15 If Fracs(j, 1) = sIN Then sIN = Fracs(j, 2) '<-or Fracs(j, 3) for HEX Exit For End If Next j MakeFracs = VBA.Replace(Arg, sOUT, sIN) Else MakeFracs = Arg End If Else MakeFracs = Arg End If End Function


 
At least that's all I wanted it to do. In practice, Wikipedia and WordPress seem to be not fully onboard with HTML5 and do not handle all fifteen &fracnd; formats (I confirmed Firefox does). That added "j-loop" in the middle translates the &fracnd;'s into Dec code. This works fine, though it's a step back from HTML5. Option is given to use Hex if desired. Your Excel table then looks like this:

N O P Q R S T U V
1 1/1 &#189; &#8531; &#188; &#8533; &#8537; 1/7 &#8539; 1/80
2 2/1 2/2 &#8532; &#189; &#8534; &#8531; 2/7 &#188; &#188;.
3 3/1 3/2 3/3 &#190; &#8535; &#189; 3/7 &#8540; &#8540;A
4 4/1 4/2 4/3 4/4 &#8536; &#8532; 4/7 &#189; 6 &#189;
5 5/1 5/2 5/3 5/4 5/5 &#8538; 5/7 &#8541; 7 &#8541;
6 6/1 6/2 6/3 6/4 6/5 6/6 6/7 &#190; 8 &#190;
7 7/1 7/2 7/3 7/4 7/5 7/6 7/7 &#8542; 9 &#8542;
8 8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

 
Where N1: =MakeFracs(D1) filled down and right. Arranged that way you can see the HTML5 design thoughts. The very ugly website would look like this:

1/1 ½ ¼ 1/7 1/80
2/1 2/2 ½ 2/7 ¼ ¼.
3/1 3/2 3/3 ¾ ½ 3/7 ⅜A
4/1 4/2 4/3 4/4 4/7 ½ 6 ½
5/1 5/2 5/3 5/4 5/5 5/7 7 ⅝
6/1 6/2 6/3 6/4 6/5 6/6 6/7 ¾ 8 ¾
7/1 7/2 7/3 7/4 7/5 7/6 7/7 9 ⅞
8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

 
Frankly, I'm not sure that this is an improvement. You'll come across Wikipedia editors, however, who are convinced of it. I thought about adding a trailing space, as in MakeFracs = VBA.Replace(Arg, sOUT, sIN & Chr(32)), but for every time I wanted to, I thought of an example where I didn't, and the logic got very convoluted. Better I decided to put the space in the table where wanted and not in the function. My HTML tablemaker is here, but it's being overcome by the hard steady march of technology. Wiki and CSS tablemakers are coming up. I used MakeFracs() in the above. No fractions were harmed in the making of this post.

…mrt
©¿©¬

Working with the MAXIF() and LARGEIF() functions

Oh, Wait! Excel doesn’t have those functions. We’ll have to roll our own. We’re going to use the Double Unary tip found here. In Excel, 1*TRUE = 1, 0+TRUE = 1, TRUE^1 = 1, and minus-minus TRUE (–TRUE, the double unary) = 1. It’s two sign changes, and thought to be the best way to turn an array of TRUE/FALSE into ones/zeros. Multiplying an array of parameters by an array of ones and zeros leaves only the TRUE parameters non-zero.

Back to our list of the 122 major teams in professional sports, the Boss has decided that sorting by name length, then alphabetically, isn’t going to do. He wants it by name length by stadium size. So, thanks to Wikipedia, your data looks like this, Column(F) is arena size:

  D E F
1 Anaheim Ducks 13 17,174
2 Arizona Cardinals 17 63,400
3 Arizona Diamondbacks 20 48,633
4 Atlanta Braves 14 50,097
5 Atlanta Falcons 15 71,228
6 Atlanta Hawks 13 18,729
7 Baltimore Orioles 17 45,363
8 Baltimore Ravens 16 71,008
9 Boston Bruins 13 17,565
10 Boston Celtics 14 18,624

Using fzz’s comment about the ROW() function, we’ll sort the length by G1=LARGE($E$1:$E$122,ROWS($G$1:$G1)) filled down. The table looks much like before:

  D E F G
1 Anaheim Ducks 13 17,174 29
2 Arizona Cardinals 17 63,400 22
3 Arizona Diamondbacks 20 48,633 22
4 Atlanta Braves 14 50,097 22
5 Atlanta Falcons 15 71,228 21
6 Atlanta Hawks 13 18,729 21
7 Baltimore Orioles 17 45,363 21
8 Baltimore Ravens 16 71,008 20
9 Boston Bruins 13 17,565 20
10 Boston Celtics 14 18,624 20

We know there are only three teams of name length 22. A formula – -($E$1:$E$122=22), using the double unary operation, gives us an array of 119 zeros, and just 3 ones that line up on 22. If we multiply that array by the array of arena capacities (F1:F122) we have 119 multiplications by zero, and three multiplications by 1. A MAX() function or an equivalent LARGE(,1) function on that array product returns the capacity of largest stadium hosting a team name 22 letters long. Those equations formulas, array entered, would look like this:

  • {=LARGE(- -($E$1:$E$122=22)*$F$1:$F$122,1)}
  • {=MAX(- -($E$1:$E$122=22)*$F$1:$F$122)}

We want to modify those for different teams. Changing the =22 to =$G1 is a start, and we’ve gone about as far as we can with our ‘MAXIF().’ It’s going to return the same thing three times. A scheme using COUNTIF() starting from the top and working down will improve the ‘LARGEIF()’. Using mixed references, $G$1:$G1 will grow as we fill down. COUNTIF($G$1:$G2,$G2) will only count one 22, COUNTIF($G$1:$G3,$G3) will count two 22s, and COUNTIF($G$1:$G4,$G4) will count 3, and that’s all there are. This array-entered equationformula then, filled down, is ‘LARGEIF()’:

  • {=LARGE(- -($E$1:$E$122=$G1)*$F$1:$F$122,COUNTIF($G$1:$G1,$G1))}

In I1 filldown =MATCH(H1,$F$1:$F$122,0) and in J1 filldown =INDEX(D:D,I1). Your table should look like this:

  D E F G H I J K
1 Anaheim Ducks 13 17,174 29 45,389 52 Los Angeles Angels of Anaheim  
2 Arizona Cardinals 17 63,400 22 19,980 95 Portland Trail Blazers  
3 Arizona Diamondbacks 20 48,633 22 19,356 63 Minnesota Timberwolves  
4 Atlanta Braves 14 50,097 22 18,144 30 Columbus Blue Jackets  
5 Atlanta Falcons 15 71,228 21 43,651 89 Philadelphia Phillies  
6 Atlanta Hawks 13 18,729 21 19,596 42 Golden State Warriors  
7 Baltimore Orioles 17 45,363 21 18,203 83 Oklahoma City Thunder  
8 Baltimore Ravens 16 71,008 20 68,756 69 New England Patriots  
9 Boston Bruins 13 17,565 20 67,164 49 Jacksonville Jaguars  
10 Boston Celtics 14 18,624 20 65,857 108 Tampa Bay Buccaneers  

Column(I) shows the indices reordered by size of the arena.

There are some problems with this approach. It only works with positive numbers, such as our capacities. Negative numbers will be less than a FALSE, and a zero will be returned in their place. And if ever capacities are equal, it will always return only the first (same old problem). Nicely here, hockey and basketball held in the same arena draw to different capacity. The file is available at http://wl.filegenie.com/~JMOprof/LargeIF.xls

…mrt
©¿©¬

Finding the 2nd, 3rd, 4th … MATCH()

Excel’s MATCH() function will return the relative location of the first occurrence that satisfies the match criterion (the lookup_value) within the specified array (the lookup_array).  Most often, this is what you want.  For instance, if you have duplicate entries, returning the first match is no different than returning the last match.  However, if you have unique entries with the duplicated criterion, how do you return all the unique values?

For example, you have a list of the 122 major professional sports teams (32 NFL, and 30 MLB, NBA, and NHL) sorted alphabetically in Column D.  In Column E you have code that returns the length of the team name, i.e. =LEN(D1).  Your data might look like this:

D E
1 Anaheim Ducks 13
2 Arizona Cardinals 17
3 Arizona Diamondbacks 20
4 Atlanta Braves 14
5 Atlanta Falcons 15
6 Atlanta Hawks 13
7 Baltimore Orioles 17
8 Baltimore Ravens 16
9 Boston Bruins 13
10 Boston Celtics 14

For no good reason we want to create a descending sort on name length. You can do it from the Sort selection, but we also want to keep the alpha-sort. In F1 you put =LARGE($E$1:$E$122,ROW()) and fill down. The ROW() function will cause the return of the first largest, then the second largest etc. Then you want to know how far down Column E those descending numbers fall, so that you can Index the name list that amount. In G1 put the code =MATCH(F1,$E$1:$E$122,0) and fill down. You’ll see from the data that the longest name is 29 letters, and it’s in ROW(52).

D E F G
1 Anaheim Ducks 13 29 52
2 Arizona Cardinals 17 22 30
3 Arizona Diamondbacks 20 22 30
4 Atlanta Braves 14 22 30
5 Atlanta Falcons 15 21 42
6 Atlanta Hawks 13 21 42
7 Baltimore Orioles 17 21 42
8 Baltimore Ravens 16 20 3
9 Boston Bruins 13 20 3
10 Boston Celtics 14 20 3

You can begin to see the problem: There are three teams with a 22-letter name, three with a 21-letter name, and at least 3 (in fact 13) with a 20-letter name. If in H1 we put =INDEX(D:D,G1) and filldown, we get:

D E F G H
1 Anaheim Ducks 13 29 52 Los Angeles Angels of Anaheim
2 Arizona Cardinals 17 22 30 Columbus Blue Jackets
3 Arizona Diamondbacks 20 22 30 Columbus Blue Jackets
4 Atlanta Braves 14 22 30 Columbus Blue Jackets
5 Atlanta Falcons 15 21 42 Golden State Warriors
6 Atlanta Hawks 13 21 42 Golden State Warriors
7 Baltimore Orioles 17 21 42 Golden State Warriors
8 Baltimore Ravens 16 20 3 Arizona Diamondbacks
9 Boston Bruins 13 20 3 Arizona Diamondbacks
10 Boston Celtics 14 20 3 Arizona Diamondbacks

This is exactly what we wanted to avoid. So, using Col(H) now as a helper column, in H1 put =G1. In H2 put

  • =IF(G2<>G1,G2,H1+MATCH(F2,INDIRECT(“E”&(H1+1)&”:E122″),0))

and fill down. If the value did not repeat, just copy it over; and if it didn’t, we’re making a “sliding range” that starts one row after the start of a repeated pair, ends at the end of the data, and “slides” down Column E. The sliding range is made by the INDIRECT() function. It builds the range one row down (H1+1) from the first of a repeating match. We add H1 to that result, and we have our index down Column D. This may be better seen in H3, adjacent to G3, where the first repeat starts.

  • =IF(G3<>G2,G3,H2+MATCH(F3,INDIRECT(“E”&(H2+1)&”:E122″),0))

The IF() is false. We are looking for the relative location of the lookup_value (22) in a new lookup_range (E31:E122). It’s 33 rows in. We add the original 30 to that, and the index is now 63. A similar thing happens in H4. H5 is G5 copied over. In Column I, I1 =INDEX(D:D,H1), filled down. The resulting table:

D E F G H I
1 Anaheim Ducks 13 29 52 52 Los Angeles Angels of Anaheim
2 Arizona Cardinals 17 22 30 30 Columbus Blue Jackets
3 Arizona Diamondbacks 20 22 30 63 Minnesota Timberwolves
4 Atlanta Braves 14 22 30 95 Portland Trail Blazers
5 Atlanta Falcons 15 21 42 42 Golden State Warriors
6 Atlanta Hawks 13 21 42 83 Oklahoma City Thunder
7 Baltimore Orioles 17 21 42 89 Philadelphia Phillies
8 Baltimore Ravens 16 20 3 3 Arizona Diamondbacks
9 Boston Bruins 13 20 3 49 Jacksonville Jaguars
10 Boston Celtics 14 20 3 53 Los Angeles Clippers

The names are now sorted by length, by alphabet. INDIRECT() is volatile, so there is a penalty for this. For extra credit, and no peeking, what’s the shortest name?

…mrt
©¿©¬