Daily Dose of Excel

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:

 
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:

 
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:

 
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.

 
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:

 
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:

 
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
©¿©¬

You’ve probably watched The Price is Right TV game show. On it they run the Clock Game.

The game is played for two prizes. The actual price of the first prize is shown to the studio and home audiences. After the contestant gives their first bid, a 30 second clock is started and the host tells the contestant whether the actual price is higher or lower than the bid. The contestant continues to bid, responding to the host’s clues, until either the contestant wins by correctly guessing the price of the prize or the time expires. If time remains after the first prize is won, the process is repeated for the second prize.…With few exceptions, only prizes valued below $1,000 have traditionally been offered in the Clock Game.

The algorithm to use is simple:

• Pick a ceiling ($1000) and a floor ($0)
• Bid the average ($500)
• If the bid is too low, the bid becomes the floor
• If the bid is too high, the bid becomes the ceiling
• Repeat

Being computer types, we’ll put the ceiling at $1024. For a $407-priced prize, your guesses would look like this:

 
Ten guesses, and you’ve won a washing machine. Here are the formulas that make this work.

• D1: = 407 (the unknown price)
• C3: = 0 (the initial floor)
• F3: = 1024 (the initial ceiling)
• D3: = (C3+F3)/2 (512—the initial bid)
• E3: = IF(D3=$D$1,”Stop”,IF(D3>$D$1,”Too High”,”Too Low”)) (the host’s clues)
• C4: = IF(E3=”Stop”,”",IF(E3=”Too Low”,D3,C3))
• D4: = IF(E3=”Stop”,”",(C4+F4)/2)
• E4: = IF(D4=$D$1,”Stop”,IF(D4>$D$1,”Too High”,”Too Low”))
• F4: = IF(E3=”Stop”,”",IF(E3=”Too High”,D3,F3))

Watch out for “curly quotes” if you copy and paste in. Filldown C4:F13. So what’s the point? We knew the “unknown price” going in. Here’s a recent prospective employee question the BBC got from Qualcomm:

Given 20 ‘destructible’ light bulbs (which break at a certain height), and a building with 100 floors, how do you determine the height the light bulbs break?

You watch The Price is Right or you read DDoE, and you think “Clock Game!” In 20 bulbs, if they break from a 407.407 foot drop, and a floor = 10 feet:

 
You’re 2-ten-thousandths of a foot off. You get the job, a great way to start the new year.

 
…mrt
©¿©¬

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:

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:

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:

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
©¿©¬

When you carol along with The Twelve Days of Christmas do you think of spreadsheets? I do, and that certainly says all you need to know about my musical ability. The folks at PNC Wealth Management do also. Annually they issue their Christmas Price Index, with the 2011 version here. Spreading the data out, The Twelve Days of Christmas looks like this:

PNC provides the Bundled Cost. We have to work backwards to find the Unit Cost. In other words, eight total maids-a-milking cost $58. The data sources are at the Wiki link. The poor dairy lasses are working minimum wage.

For DDoE, PNC’s table is a good way to review the concept of named ranges. Define Christmas as =Sheet1!$B$2:$M$14, _12th_Day as =Sheet1!$M$2:$M$14, Bundled_Cost as =Sheet1!$P$2:$P$14, Maids as =Sheet1!$B$6:$P$6, Quantity as =Sheet1!$N$2:$N$14, and Unit_Cost as =Sheet1!$O$2:$O$14.

The total number of gifts can be =SUM(Christmas) or =SUM(Quantity), being 376 (more on this later). The cost of the 12th Day of Christmas can be =SUM(Bundled_Cost) or = SUMPRODUCT(_12th_Day,Unit_Cost), being $24,263.18. The total expenditure of your True Love is =SUMPRODUCT(Quantity,Unit_Cost), or $101,119.84. A very generous person, your True Love.

We can use the Intersection Operator, a space, as =Maids Quantity, to find that we need 40 work hours from them. The value of named ranges is in the legibility it brings to your spreadsheet. You can expand this until you cover all days and all gifts. The work is in the defining of the names, the benefit is in the presentation.

PNC says the total gifts number 364. Surely a partridge and a pear tree count as two gifts in your household, no? Happy Holidays!

…mrt
©¿©¬

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:

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).

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:

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:

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
©¿©¬

Part of my part-time job schedules world-wide PC-chat conferences weekly, and I announce the time referenced to the East Coast. As daylight savings time is about to end, I wanted an algorithm that knew whether standard time or daylight savings time was in effect.

As I’m not worried about the 2:00AM change over, I can do it based on the date. Since 2007, if the month is December through February, standard time is in effect. If the month is April through October, daylight savings time is in effect. Daylight savings time starts the second Sunday in March, and ends the first Sunday in November.

So, by counting Sundays in March and November, I can toggle the time zone. This is what I came up with.

The default WEEKDAY() function returns 1 for Sunday, so if there are 2 Sundays in March including the day in question, turn IsDST to TRUE, and stop the loop. Conversely, if there is 1 Sunday in November including the day in question, leave IsDST as FALSE, and stop the loop.

I use the VBA. leader to ensure this function works on a Mac. Needless to say, it also works in MSWord.

…mrt
©¿©¬

Specifically, MacPaint files. Why MacPaint? 1. With a maximum size of 576 pixels wide by 720 pixels tall, MacPaint images fit on a modern spreadsheet when using cells as pixels, and 2. MacPaint prototyped the lossless PackBits compression algorithm, which is the default compression scheme for TIFF.

PackBits is a signed-byte file structure with flag-counter bytes interspersed with data bytes. If the flagbyte is negative, the next byte (always a data byte) is repeated, zero-based, the two’s-complement of the flagbyte times. If the flagbyte is a positive number, the next number of bytes are copied directly. Each data byte represents the status of eight pixels, with 1 indicating a black pixel and 0 representing a white pixel, as shown below.

The PackBits algorithm is defined here. The following code, cross-posted to Wikipedia, handles Apple’s demonstration case.

It works out then that a PackBits stream is a flagbyte followed by data bytes followed by a flagbyte followed by data bytes… Each flagbyte indirectly points to the location of the next flagbyte.
 
Excel’s VBA does not have signed bytes, but if we look at the flagbyte’s value, and “Select Case” all flagbytes greater or equal to 128 (b10000000) we have a test that handles negative flag bytes. The two’s complement of a number N is 2^B-N, where B is the word size, in our case, 8. In other words, the two’s-complement of the negative flagbyte is 256 minus the value of the flagbyte.
 
The MacPaint file has a 512-byte header that we can ignore. It also can have, as data bytes, internal bytes that would otherwise be read as end-of-file. We handle that by reading the file in for binary access.
 
The basic approach is to read in the file similarly to a text file. Turn the flagbytes characters into ascii, with the ascii as the count to copy or to take the two’s complement of. Byte(513) has to be a flagbyte. The data bytes are input as characters, turned to ascii, then turned to byte-strings. The cells are turned black or white by looping through the byte-string. After 576 1′s and 0′s (or 72 data bytes) a new row is started. The file structure does not reveal the length of the data, but we can infer that from the file’s length.

What I don’t like about the code is that for the case of Count>=128, the repetitive bytes, I’m parsing it Count times. Rather I should parse it once and copy it Count times. I just don’t see how. I know somebody out there does.
 
If you want the MacPaint copy of Hokusai’s The Great Wave off Kanagawa shown above, it’s here. A nice thing about the Excel code is that you can change the color indices to suit. An appropriate blue might be color index 32, RBG(0,0,255).

To show why PackBits is still used, the lossless JPEG used above for HTML purposes is 211 Kbytes. The lossless Mac counterpart is 17 Kbytes. While a MacPaint file can define 576*720 pixels (0.4 megapixels), its maximum size is 512 + (72*90) bytes, or just over 51 Kbytes.

 
…mrt

Last week, more absent-minded than usual, I forgot the software I was in. I looked for the Change Case … format dialog in Excel. It’s not there, of course. It’s in Word. It’s in PowerPoint. It’s not in Excel. So I took a morning and wrote one.

Word gives you five options to change case: Sentence case, lower case, UPPER CASE, Title Case, and tOGGLE cASE. Word’s “Title Case” is a misnomer. It’s really what we think of as PROPER() case–every word capitalized. So my version has six options, including a true Title Case in accordance with the government style manual for capitalization of short words, and a Proper Case that does exactly as MSWord does in its Title Case.

Considering The Grapes of Wrath as illustrative:

• Sentence case returns: The grapes of wrath
• lower case returns: the grapes of wrath
• UPPER CASE returns: THE GRAPES OF WRATH
• Title Case returns: The Grapes of Wrath
• tOGGLE cASE returns: tHE gRAPES OF wRATH
• Proper Case returns: The Grapes Of Wrath. Note the captialized of.

Lower case, upper case, and proper case were easy. They are just applications of LCase(), UCase(), or Application.Worksheetfunction.Proper() to the selection contents.

Title case starts with capitalizing every word and then swapping in the lower case versions of the one, two, and three letter words, when found between two spaces. Two spaces protects against an inappropriate changeout of a leading article.

Sentence case is a recursive search for full-stop punctuation (periods, exclamation points, question marks, and colons) reading left to right. The exit condition is when there nothing more found to the right.

Toggle case adds or subtracts 32 to the ascii-code of each letter to change its case. Upper to lower, add 32. Lower to upper, subtract 32. Non-letters are unchanged. The Like operator is used to for the comparisons in both the sentence and toggle cases.

The macro module code is a one-liner. All the execution is in the form’s code.

Code for the form:

I wanted to use the antique char|32 and char&32 to do the toggle-case change, but I couldn’t find the right syntax. Wiki says it’s not any faster these days, but readers may want to comment.

Tested on a PC and a Mac (using XL2011). The Mac does not appear to support tool-tips from the form. The Mac version looked better in Tahoma 12, the PC version in Tahoma 10. I compromised.

The form is available here.

…mrt

GPS tells you where you are in Decimal Degrees (DD), with positive degrees being north latitude or east longitude, and negative degrees being south latitude or west longitude. Most charts (I was a nautical type before I was an Excel type. Think maps if that’s not you) tell you position in degrees-arc minutes-arc seconds (DMS). While one can “eyeball” the conversion from DD to DMS, let’s get more specific. Thanks to Wikipedia, the US Capitol is at 38.889722°, -77.008889°. Ignoring what we might otherwise know, by the GPS sign conventions the Capitol has a north latitude and a west longitude. Making the conversion is about handling remainders. Walking through converting the Capitol’s longitude, the degrees are:

• D = INT(ABS(-77.008889)) or 77

The remainder R is:

• R = ABS(-77.008889) – D
• R = 77.008889 – 77
• R = 0.008889

To convert to arc-minutes:

• M = R*60
• M = 0.008889*60
• M = 0.533340
• M = INT(M) = INT(0.533340)
• M = 0, but always formatted “00″

The remainder is:

• R = 0.533340 – M*60
• R = 0.533340 – 0*60
• R = 0.533340

To convert to arc-seconds:

• S = R*60
• S = 0.533340*60
• S = 32.000400
• S = INT(S) = INT(32.000400)
• S = 32

The Capitol is at 77 degrees, 00 minutes, 32 seconds, and remembering the minus sign, west. Or 77° 00′ 32? W. Alternatively, 77° 00′ 32″ W with ascii substitutions for prime and double prime.

Turning that into VBA, with an optional Boolean to specify if you want north/south or east/west, and another option for Ascii representation, the DD2DMS conversion looks like this:

The degree character is ascii(176). That’s actually more concise than my explanation. ;-(

Can it be done in a spreadsheet formula? Of course, but it’s complicated, and that’s the reason for the somewhat tedious example above to walk through. Formulas do modular arithmetic differently than VBA. In a formula MOD(ABS(-77.008889),1) equals 0.008889. In VBA, ABS(-77.008889) Mod 1 equals zero. We’ll make use of this difference to find our remainders. Assuming the Capitol’s decimal longitude is in H8, then in four parts:

Arc-minutes:

Arc-seconds:

Note the nested MOD(,1)’s to get the second remainder. Lastly for east/west:

The longitude is the concatenation of all of these:

Complicated. Can it be done as a number format? Since we are talking minutes and seconds suggests yes. However Excel doesn’t process negative times, so the answer will be completely right only for that quarter of the world that is in the northern latitudes and eastern longitudes. Assuming again H8 holds the longitude, than in another cell, =ABS(H8/24). Custom format that cell as

• [h]° mm’ ss”

You enter the degree symbol by holding down the alt-key and keying 0176 on the keypad. How to get the north/south or east/west right will require another cell.

There’s two and a quarter ways to make the DD2DMS conversion. A second of angle on the surface of the earth is about 30 meters or 100 feet. … Degrees, given to three decimal places (1/1000 of a degree), have about 1/4 the precision as degrees-minutes-seconds (1/3600 of a degree), and so identify locations within about 120 meters or 400 feet. That’s not true as you add decimal places, of course, and the decimal to sexagesimal (Base10 to Base60) conversion becomes inexact.

More in this in the next post: How to go DMS2DD.

…

Spreadsheets have their CHAR() function, and VBA has its Chr() function. Both return the text character for the specified numerical input, 0 to 255. And spreadsheets have their CODE() function, and VBA has its Asc() function. Both of those return the ASCII code for the leading character of a text string. All well-worn stuff.

But what if you want or need to work with Unicode values? All four functions fail you. As an example, assume you want the true prime character (‘, Unicode 2032) in a string. The prime character, technically, is not an italicized apostrophe (‘), a right single curly quote (‘), or an acute accent (‘).

VBA provides the ChrW() function that does that. ChrW() expects a long as input, but also accepts hexadecimal. Unicode is in hex numbering, so there are two choices: Change U2032 to decimal, or tell ChrW() that the input is in Hex. Since HEX2DEC(2032) is 8242, these two are equivalent:

• ChrW(8242)
• ChrW(&H2032)

Both will put ‘ into a string. If ChrW() repeated the same functionality of Chr() below 256, things would be simple. However, the Windows character set deviates from the Unicode character set for ASCII(128) to ASCII(159). In that range, Chr(CharCode) and ChrW(CharCode) produce different results. As WikiPedia says, Windows “coincides with ISO-8859-1 for all codes except the range 128 to 159 (hex 80 to 9F), where the little-used C1 controls are replaced with additional characters.” Not sure what C1 controls (probably a printer), but if we want to get Unicode to the spreadsheet, do we want it to give the functionality of CHAR()/Chr(), or that of ChrW() which is ISO-8859-1 compliant? Or, optionally both. The function CHARW() takes the optional route. If you set Exact_functionality to TRUE, you can put those C1 controls in your spreadsheet. The default is to do otherwise.

One very nice thing is that you can feed Clng() a hex value, and it will do the HEX2DEC conversion for you.

The VBA function AscW() goes the other way, and has the same ISO problems. It will tell you the decimal code of the first character in a Unicode string, with no regard to the Windows character set. We can make another UDF CODEW() that can optionally specify either the decimal or hex value for the first character is returned, and whether or not to be ISO compliant. The default is to return the HEX unicode (as Uxxxx) and not to comply.

The default will return U2032 when the first character is ‘, and 8242 when Unicode_value is set FALSE. For another example, € is CHAR(128), Chr(128), ChrW(8354), CHARW(128), CHARW(“U80″), CHARW(“U20AC”,TRUE) and CHARW(8364,TRUE).

CODEW(“€”) is “U80″, CODEW(“€”,FALSE) is 128, CODEW(“€”,,TRUE) is “U20AC”, and CODEW(“€”,FALSE,TRUE) is 8354.

To see Unicode characters, the cell’s font has to be set to a Unicode font.

…mrt