Keyboard shortcuts

Source: link

The purpose of a Chart

The purpose of a chart is to visualize something. Let the chart match the purpose.

Source: picture2, picture3, picture1, chart chooser

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

Concatenate a Range with Separator (optional)

The following custom function concatentates the data in a range...

ALT. 1: Source: link

....................

Function AConcat(a As Variant, Optional Sep As String = "") As String

    'By Harlan Grove, March 2002

    Dim Y As Variant

    If TypeOf a Is Range Then

        For Each Y In a.Cells

            AConcat = AConcat & Y.Value & Sep

        Next Y

    ElseIf IsArray(a) Then

        For Each Y In a

            AConcat = AConcat & Y & Sep

        Next Y

    Else

        AConcat = AConcat & a & Sep

    End If

   

    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))

   

End Function

----------------------------------------------------------

ALT2: link

...........

Function ConcRange(Substrings As Range, Optional Delim As String = ",", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = True)
      
    Dim CLL As Range
    
    For Each CLL In Substrings.Cells
        If Not (SkipBlanks And Trim(CLL) = "") Then
            ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value))
        End If
    Next CLL
     
    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
     
End Function

Smart solution by Oscar

Problem: 

I have table similar like this:

BatTodor

A 5
B 2
C 1
D 4
Is it possible with formula to generate list like this:
A
A
A
A
A
B
B
C
D
D
D
D

Solution

Array formula in cell A6:

=INDEX($A$1:$A$4,MATCH(FALSE, COUNTIF($A$5:A5,$A$1:$A$4)=$B$1:$B$4,0))

Source: link

Calculate work hours

Good to know

Cell E1:H8

Day	Start	End	Hours
Sunday			0:00
Monday	08:00	18:00	10:00
Tuesday	08:00	18:00	10:00
Wednesday	08:00	18:00	10:00
Thursday	08:00	18:00	10:00
Friday	08:00	18:00	10:00
Saturday	08:00	13:00	5:00

Cell A1:C4

Start	End	Hours
2009-02-14 Sat  08:00	2009-02-15 Sun 13:00	05:00
2002-09-09 Mo 12:00	2012-09-12 Thu 03:00	26:00
2001-01-21 Sun 08:00	2001-01-22 Mon 15:51	07:51

=SUMPRODUCT(INT((WEEKDAY(A2-{1;2;3;4;5;6;7})+INT(B2)-INT(A2))/7),H$2:H$8)-MEDIAN(0,MOD(A2,1)-INDEX(F$2:F$8,WEEKDAY(A2)),INDEX(H$2:H$8,WEEKDAY(A2)))-MEDIAN(0,INDEX(G$2:G$8,WEEKDAY(B2))-MOD(B2,1),INDEX(H$2:H$8,WEEKDAY(B2)))

Reference: link (+Excel file)

=N(OFFSET({array})) formula

Good to remember

Both N() and T() have other more important uses, they can be used to produce a usable array from Offset(Ref,{Array},0) that can be processed by other worksheet functions

Ex Assuming we have Text data from A1:A10
=Counta(Offset(A1,{1,5,6},0)) Array entered will give 1
However
=Counta(T(Offset(A1,{1,5,6},0))) will give 3
Like wise N() for numbers

Reference: link

SmartPivot - Multi charts from one Pivottable

Smart add-in:

Reference: linkhttp://rquintino.wordpress.com/2012/03/29/smartpivot-for-excelnow-for-the-new-olap-features/

Picture into Excel from imageurl

Dim url_column As Range
Dim image_column As Range

Set url_column = Worksheets(1).UsedRange.Columns("A")
Set image_column = Worksheets(1).UsedRange.Columns("B")

Dim i As Long
For i = 1 To url_column.Cells.Count

  With image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value)
    .Left = image_column.Cells(i).Left
    .Top = image_column.Cells(i).Top
    image_column.Cells(i).EntireRow.RowHeight = .Height
  End With

Next

Reference: link