Månad: januari 2020

Auto-format Excel scientific notation in Word

Typically when you copy and paste data from Excel to Word you get the ugly scientific notation that Excel uses, of the kind 3.23E+03 or 6.29E-07, which are hard to read, at least if you have been thaught that it should really be written like 3.23×103.

Re-formatting by hand is tedious and can be really time consuming of you have many entries. Therefore I created a macro that will automatically look for all #.##E+-# and replace them with the more pleasantly looking #.##×10# . You can copy the below code and insert in the VBA editor as a new macro.

[code] Sub scientificnotation() ’ put in general form Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = ”([0-9.]@)E([-+0-9]@)([0-9]{2;3})” .Replacement.Text = ”\1## × 10##\2\3##” .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchAllWordForms = False .MatchSoundsLike = False .MatchWildcards = True End With Selection.Find.Execute Replace:=wdReplaceAll ’ take out leading 0 exponents Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = ”##+0” .Replacement.Text = ”##+” .Forward = True .Wrap = wdFindContinue .Format = True .MatchCase = False .MatchWholeWord = False .MatchAllWordForms = False .MatchSoundsLike = False .MatchWildcards = False End With Selection.Find.Execute Replace:=wdReplaceAll ’ take out + exponents Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = ”##+” .Replacement.Text = ”##” .Forward = True .Wrap = wdFindContinue .Format = True .MatchCase = False .MatchWholeWord = False .MatchAllWordForms = False .MatchSoundsLike = False .MatchWildcards = False End With Selection.Find.Execute Replace:=wdReplaceAll ’ take out leading 0 exponents for negative numbers Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = ”##-0” .Replacement.Text = ”##-” .Forward = True .Wrap = wdFindContinue .Format = True .MatchCase = False .MatchWholeWord = False .MatchAllWordForms = False .MatchSoundsLike = False .MatchWildcards = False End With Selection.Find.Execute Replace:=wdReplaceAll ’ free up ×10 Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = ”## × 10##” .Replacement.Text = ”×10##” .Forward = True .Wrap = wdFindContinue .Format = True .MatchCase = False .MatchWholeWord = False .MatchAllWordForms = False .MatchSoundsLike = False .MatchWildcards = False End With Selection.Find.Execute Replace:=wdReplaceAll ’ elevate exponents Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find.Replacement.Font .Superscript = True .Subscript = False End With With Selection.Find .Text = ”##([-+0-9]@)##” .Replacement.Text = ”\1” .Forward = True .Wrap = wdFindContinue .Format = True .MatchCase = False .MatchWholeWord = False .MatchAllWordForms = False .MatchSoundsLike = False .MatchWildcards = True End With Selection.Find.Execute Replace:=wdReplaceAll End Sub [/code]

This macro can also be installed through the macro enable Word template below. Just download and put in the auto load folder for Word.

Scientific_notation.dotmLadda ner

 

Simulating greywater reuse in a residential building

The last few weeks I’ve been coding a simulator for greywater reuse scenarios based on real water usage data from HSB Living Lab. The input data is disaggregated hot and cold water consumption, with a time resolution of 10 minutes.

Simulated tank levels in the greywater treatment system

The simulation code allows the researcher to define from what micro-use points greywater is to be collected, and for what purposes it should be reused. The simulation of the treatment system itself is rather archaic, but the output is still kind of cool. The graph above show the levels in the untreated and treated water tanks over a period of 8 days. Tank volumes are 0.3 cbm and the treatment capacity in this run was set to 2 liters per minute, while collecting from sink and shower and reusing for sink, shower and WC. This scenario typically results in 50-60% reduction in total mains hot water consumption and 20-30% total mains cold water consumption.

Another cool feature of the simulator is the possibility to incorporate rain harvesting from real precipitation data into the equation. In the Hadley-cell zone wich defines Gothenburg this gives some pretty interesting numbers.

The results from this simulation should appear in a journal paper ”shortly” (meaning maybe before summer 2020).