Windows new BI release (Power BI)

Tech features

Source: http://www.powerpivotblog.nl/power-bi

Unpublished: Geoflow, Windows 8.1 release, SQL, Tableau Public, Motion charts, Index formula, 2, Vertical Bar & Line chart

Excel Add-ins - Statistical analysis (not tested)

Note: I have not tried these Excel add-ins, yet.




Link: imDEV, blogg, sourceforge

Link: NumericalAnalysis (PCA;PrincipalComponentAnalysis, PLS;PartialLeastSquare)









Unpublished: Engauge Digitizer (from picture to data)

Statistical distribution curves

"There are a dozen probabilistic simulation add-ins on the market. For many reasons, Crystal Ball is clearly the best of both."

Source: link, video, xls1, xls2, common distribution curves, create sample, Charts in Period table

Unpublished: trigger macros, E90E50 fx, ExcelHero - LinkedIn group

Excel - Microsoft Data Explorer add-in (preview)

"In a nutshell, Data Explorer is self-service ETL for the Excel power user – it is to SSIS what PowerPivot is to SSAS. In my opinion it is just as important as PowerPivot for Microsoft’s self-service BI strategy.
Microsoft Data Explorer add-in (preview) is supported on Excel 2013 and Excel 2010 SP1".

Source: link

Substitute multiple text strings

Formula:

=SubstituteMultiple(Text; Old_Text; New_Text)

VBA code

Function SubstituteMultiple(text As String, old_text As Range, new_text As Range)
Dim i As Single
For i = 1 To old_text.Cells.Count
    Result = Replace(LCase(text), LCase(old_text.Cells(i)), LCase(new_text.Cells(i)))
    text = Result
Next i
SubstituteMultiple = Result
End Function

Source: link

Language translation formula (Excel)

Excel formula that can translate From any language To any language.
Example: getGoogleTranslation("Do you speak English?";"en";"sv")

However this code has one limitation; it can only translate one sentence.


--
Public Function getGoogleTranslation(strSource As String, strSourceLang As String, strDestLang As String) As String
    Dim strURL As String, x As String

    strURL = "http://translate.google.com/translate_a/t?client=t&text=" & _
             Replace(strSource, " ", "%20") & _
             "&hl=en&sl=" & strSourceLang & _
             "&tl=" & strDestLang & "&multires=1&pc=0&rom=1&sc=1"

    With CreateObject("msxml2.xmlhttp")
        .Open "get", strURL, False
        .send
        x = .responseText
    End With

    getGoogleTranslation = Replace(Replace(Split(x, Chr(34) & "," & Chr(34))(0), "[", ""), """", "")

End Function
--

Source: link

Extract hyperlink formula

Example:
=HLINK(A1:A10;FALSE)

Function HLink(rng As Range, Optional RtnSub As Boolean) As String
 If rng(1).Hyperlinks.Count Then
    If RtnSub = False Then
       HLink = rng.Hyperlinks(1).Address
        Else
       HLink = rng.Hyperlinks(1).SubAddress
    End If
 End If
End Function

Source: link, subaddress property