Google Sheets Banker's Rounding

I needed to have a formula for Banker's Rounding and use it in Google Doc's Sheets app. This seems to work well. This only rounds to whole numbers, but could be modified to do currency pretty easily.

=if(mod(B2,0.5)>=0,IF(ISEVEN(B2),ROUNDDOWN(B2,0),ROUNDUP(B2,0)),round(B2,0))

Address Regex

Needed to do some loose validation on mailing addresses. Here's what I did.

Physical Address Format

^[\d]+ [\w\.,\- ]+([\d]{5}(-[\d]{4})?)( .+)?$


Mailing Address Format

^[\w\.,\- ]+(\d{5}(-\d{4})?)( .+)?$


PO Box Format (used to exclude PO Boxes, taken from http://forums.asp.net/t/1551081.aspx?regular+expression+for+P+O+Box+validation

[P|p]*(OST|ost)*\.*\s*[O|o|0]*(ffice|FFICE)*\.*\s*[B|b][O|o|0][X|x]


NOTE: Since I'm using \w, underscores will get through this filter. I didn't think it was a big deal, but if you need to exclude underscores, it's easy to fix. The post office would apparently remove underscores if detected, as it does with all special characters other than hyphens between meaningful numbers.

I also didn't validate state, as we're feeding state through a dropdown. Regex validation would need to include every state and territory permutation (CA, Calif, California, etc...).


The last bit allows any character to be used after the zip code.

To see how the Post Office parses addresses, you can look here...

http://pe.usps.gov/cpim/ftp/pubs/Pub28/pub28.pdf

Async in .Net - Console App

I wanted to play around with Async in a console app, but most of the examples are in either winforms or mvc.

I found this article that showed exactly how to do it! Posting it to get it more coverage and so that I can get back to it if needed.

Add / Get Query Value from Uri Extension Method

Needed to pull some code from an older project that had an interesting utility. It had extension methods for adding and retrieving values from a Uri query string.

 I thought that was neat so I wrote my own version.

Angular Html Literal Directive

Yes, this is not the way you're supposed to use Angular. There might be a better workaround out there.

I needed a way to generate image elements through Angular with custom attributes, including classes and styles. I'm working in a legacy system that makes this difficult. Can't use curly braces because we need to keep using the dead jQuery Tmpl, hasn't been phased out yet. I couldn't make the angular bindHtml directive to work. It kept on removing my style and class attributes.

So I came up with this...

Angular Comparison Validation

Ahh, Angular... Why can't your documentation be as good as what I find on StackOverflow?

Thank you, Bernard Loureiro  for the code sample! From that I was able to put this together.

This directive allows you to compare two different fields on your model. The compareOperator property is really cheesie right now. If it is set, it checks to see if there's an equals and/or a greater than sign. If not, it ignores it. To make that more robust, I'd need to give some good error messages, add some documentation, etc... It works for my needs right now.

Escaping Text for CSV in TSQL

Needed string fields output in CSV, but with punctuation being used, that can be crazy. Figured it out, at least for the cases I'm looking at.


SELECT
cr.Id
,'"' + REPLACE(cr.Description, '"', '""') + '"' AS Descrption
,'"' + REPLACE(rr.Response, '"', '""') + '"' AS Response
,'"' + REPLACE(rr.Reason, '"', '""') + '"' AS Reason
,cr.ResolvedDate
FROM Reviews cr
LEFT OUTER JOIN Responses rr ON cr.Id = rr.ReviewId
ORDER BY cr.Id, rr.Id DESC