（未知标题）

From Chandoo.org

（未知标题）:

Last week Joyce asked a question on the Chandoo.org, Comment 24.

I’m wondering if there’s a way to count the number of occurrences of words when they’re all in a cell? Like this:

A1: “Windows NT, Networking, Firewalls, Security, TL, Training”

A2: “Networking, Networking, Training, Security, TL, Training”

A3: “Security, TL, Firewalls, Security, Networking, Windows NT”

Hui responded with an Array Formula:

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

As the formula is an Array Formula it is entered with Ctrl Shift Enter.

Setup the Problem

Copy the Data Above into Cells A1:A3 or download the example file here: Example File

Enter the value Security into cell C10

And array enter the formula

D10: =SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

Pull The Formula Apart

Lets take a look inside this and see how it works

We will break this formula apart and look at each section independently and then put the answers back together.

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

In a cell below the data

D13: =LEN(A1:A3) but don’t press Enter, Press F9

Excel displays ={57,56,57}

This is the number of characters in each cell A1:A3

ie: A1 has 57 characters, A2 has 56 characters, A3 has 57 characters,

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

In another cell below the data

D15: =LEN(SUBSTITUTE(A1:A3,C10,”")) but don’t press Enter, Press F9

Excel displays ={49,48,41}

What this section does is measure the length of each cell in A1:A3 but only after substituting the word being searched for from C10 with ””, which is a zero length string.

So the second array is shorter by X times the length of the word in C10

=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

Next we add up the difference between the two arrays

So you can see we have two arrays of numbers

Array 1 = {57,56,57}

Array 2 = {49,48,41}

If we subtract Array 2 from Array 1

= {57-49, 56-48, 57-41}

= {8, 8, 16}

We can do this in Excel to Check

In Cell D17 enter

=LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,”")) and press F9

Excel displays: = {8, 8, 16}

=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

The next part is to sum these up

Obviously the sum of 8, 8 & 16 is 32

We can check that

D21: =SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,”"))) and press F9

Excel displays: 32

=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

The final part of this is to divide the sum (32 in this case) by the length of the text in C10 “Security” = 8 Characters

=32 / 8

= 4

OTHER POSTS IN THIS SERIES:

You can learn more about how to pull Excel Formulas apart in the following posts

Formula Forensic 001 – Taruns Problem

WHAT FORMULAS WOULD YOU LIKE EXAMINED

If you have any formulas you would like explained please feel free to leave a post here or send me an email:

If the formula is already on Chandoo.org or Chandoo.org/Forums, simply send the link to the post and a Comment number if appropriate.

If sending emails please attach an Excel file with the formula and data

A Technique to Quickly Develop Custom Number Formats

From Chandoo.org

A Technique to Quickly Develop Custom Number Formats:

In the past Chandoo has written about custom Number Formats for cells:

http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/

http://chandoo.org/wp/tag/custom-cell-formatting/

and I have written about Custom Number Formats for Charts:

http://chandoo.org/wp/2011/08/19/selective-chart-axis-formating/

http://chandoo.org/wp/2011/08/22/custom-chart-axis-formating-part-2/

This post examines a technique for quickly developing Custom Number Formats for Cells, Charts or any other Number location in Excel.

A Technique for Quickly Developing Custom Number Formats

Instead of Selecting the cell, chart axis etc, Ctrl 1, Format Cells/Properties, Number Tab, Custom and then entering a Custom Format and Apply, only to find out that the format is incorrect, try this simple technique below.

1. Enter a few Numbers in 3 cells

Enter 3 numbers, a positive, zero and negative which have values you will expect to receive in your model.

2. Add a Custom Format Cell

In D3 I have entered ##,;-(##,);”Zero”

3. Display Numbers using the custom Format

Each Number to a display cell with a simple =Text(B3,$D$3)

Copy down

This will display the 3 numbers using the Custom Format in Cell D3

4. Develop Your Custom Format

Play around with your own Custom Number Formats to your hearts content

5. Use your new format

Once you have completed your new Custom Number Format, copy the cell contents of D3 in this case.

Select your cells/or other Excel Numbers,

Ctrl 1,

Format Cells/Properties,

Number Tab, Custom

Enter the Custom Format and Apply.

6. Extending the Technique

This technique can be extended by adding several more rows with a larger range of values.

The values are all evaluated at the same time

LIMITATIONS

The above technique does not show the effects of the Color Modifiers in the test cells

But I think it is a safe bet that you will understand what the Modifier [Red] will do

There are also reserved characters such as E

So in the above example if I had used Zero instead of “Zero”

It would have displayed Ze1900ro, where the E in Zero is taken as 10^x and x=0 so Excel interprets e as 0 or 1900, a date?

You can avoid this by using the code “Zero” or Z\ero

DOWNLOAD

You can download the worked Example File used above.

NUMBER FORMATS

For more on Number Formats check out the above links or those below:

http://www.ozgrid.com/Excel/excel-custom-number-formats.htm

http://www.ozgrid.com/Excel/CustomFormats.htm

http://peltiertech.com/Excel/NumberFormats.html

How to Look up Based on Multiple Conditions

From chandoo.org

How to Look up Based on Multiple Conditions:

This article is part of our VLOOKUP Week. Read more.

Situation

Not always we want to lookup values based on one search parameter. For eg. Imagine you have data like below and you want to find how much sales Joseph made in January 2007 in North region for product “Fast car”?

Data:

Solution

Simple, use your index finger to scan the list and find the match

Of course, that wouldn’t be scalable. Plus, you may want to put your index finger to better use, like typing . So, lets come up with some formulas that do this for us.

You can extract items from a table that match multiple criteria in multiple ways. See the examples to understand the techniques:

Using SUMIFS Formula [help]
Formula	=SUMIFS(lstSales, lstSalesman,valSalesman, lstMonths,valMonth, lstRegion,valRegion, lstProduct,valProduct)
Result	1592
Using SUMPRODUCT Formula [help]
Formula	=SUMPRODUCT(lstSales,(lstSalesman=valSalesman)*(lstMonths=valMonth)*(lstRegion=valRegion)* (lstProduct=valProduct))
Result	1592
Using INDEX & Match Formulas (Array Formula) [help]
Formula	{=INDEX(lstSales,MATCH(valSalesman&valMonth&valRegion&valProduct, lstSalesman&lstMonths&lstRegion&lstProduct,0))}
Result	1592
Using VLOOKUP Formula [help]
Formula	=VLOOKUP(valMonth&valSalesman&valRegion&valProduct,tblData2,7,FALSE)
Result	1592
Conditions:	A helper column that concatenates month, salesman, region & product in the left most column of tblData2
Using SUM (Array Formula) [help]
Formula	{=SUM(lstSales*(lstSalesman=valSalesman)*(lstMonths=valMonth)* (lstRegion=valRegion)*(lstProduct=valProduct))}
Result	1592

Sample File

Download Example File – Looking up Based on More than One Value

Go ahead and download the file. It also has some homework for you to practice these formula tricks.

Also checkout the examples Vinod has prepared.

Special Thanks to

Rohit1409, dan l, John, Godzilla, Vinod

Similar Tips

• Excel SUMIFS Formula – What is it, how to use it & examples


• Excel SUMPRODUCT Formula – What is it, how to use it & examples

Exporting Outlook Messages to Excel

From http://techniclee.wordpress.com/

Exporting Outlook Messages to Excel:

I’m writing this post primarily for Sen who in a comment to another post asked

How do i Export e-mail messages with the subject, received date & time from Outlook to Excel with the sender address?

The simplest way to carry out this is to use Outlook’s built-in export capability. Using it you can export to a .csv (comma separated values) file which you then open with Excel. However, there are a couple of drawbacks to using export. Export doesn’t allow you to pick specific messages to export so you’ll have to export an entire folder at a time. It also doesn’t allow you to limit the export to certain fields. Instead it will export everything and you’ll have to delete the columns you don’t want.

A better, but more complicated, approach is to use a macro to do the export. With a macro you can select the messages you want, export only the fields you want, and it can write directly to Excel. The code for doing this is both simple and straightforward. It creates a spreadsheet, loops though the selected messages writing the fields you want to that spreadsheet, then saves and closes the spreadsheet. This solution should work in Outlook 2003 and later.

Adding the code to Outlook.

1. Start Outlook


2. Press ALT+F11 to open the Visual Basic Editor


3. If not already expanded, expand Microsoft Office Outlook Objects


4. If not already expanded, expand Modules


5. Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.


6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window


7. Click the diskette icon on the toolbar to save the changes


8. Close the VB Editor

Sub ExportMessagesToExcel()
   Dim olkMsg As Outlook.MailItem, _
       excApp As Object, _
       excWkb As Object, _
       excWks As Object, _
       intRow As Integer, _
       strFilename As String
   strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", "Export Messages to Excel")
   If strFilename <> "" Then
       Set excApp = CreateObject("Excel.Application")
       Set excWkb = excApp.Workbooks.Add()
       Set excWks = excWkb.ActiveSheet
       'Write Excel Column Headers
       With excWks
           .Cells(1, 1) = "Subject"
           .Cells(1, 2) = "Received"
           .Cells(1, 3) = "Sender"
       End With
       intRow = 2
       'Write messages to spreadsheet
       For Each olkMsg In Application.ActiveExplorer.Selection
           'Only export messages, not receipts or appointment requests, etc.
           If olkMsg.Class = olMail Then
               'Add a row for each field in the message you want to export
               excWks.Cells(intRow, 1) = olkMsg.Subject
               excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
               excWks.Cells(intRow, 3) = olkMsg.SenderEmailAddress
               intRow = intRow + 1
           End If
       Next
       Set olkMsg = Nothing
       excWkb.SaveAs strFilename
       excWkb.Close
   End If
   Set excWks = Nothing
   Set excWkb = Nothing
   Set excApp = Nothing
   MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Using the Code.

1. With Outlook open select one or more messages from any folder.


2. Run the macro.


3. When prompted enter a filename to save the export to. You can cancel the export by not entering anything.


4. The macro will display a dialog-box when it’s finished. The dialog-box includes a count of the number of messages exported.

Notes.

• This code can easily be modified to export a different set of fields. To do that, change the headings written to the spreadsheet (lines 15-17) and the message fields (lines 25-27).


• If you don’t want the macro to prompt for a filename each time, then you can change line 7 to strFilename = “Path_and_File_Name”

Filed under: Outlook, Scripting Tagged: Outlook, VBA

VBA code to handle Access Imports and Query

From ExeclExperts.com

VBA code to handle Access Imports and Query:

Paste the following code in a general module

Public g_objConnection As ADODB.Connection
Public Const gc_strDBPath As String = "C:\Test.mdb"
Function blnConnectDatabase(strPath As String, strDBPass As String) As Boolean
'    If blnFileExists(strPath) = False Then
'        GoTo ErrH
'        Exit Function
'    End If
    Set g_objConnection = New ADODB.Connection
   On Error GoTo ErrH
   g_objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
       strPath & ";Jet OLEDB:Database Password=" & strDBPass & ";"
   On Error GoTo 0
   blnConnectDatabase = True
   GoTo ExitH
ErrH:
   blnConnectDatabase = False
   Set g_objConnection = Nothing
ExitH:
   Application.StatusBar = False
End Function
Function blnTableExistsInDB(strTableName As String) As Boolean
   Dim rst         As ADODB.Recordset
   Dim strTbl      As String
   strTbl = strTableName
   Call blnConnectDB
   Set rst = g_objConnection.OpenSchema(adSchemaTables)
   If Left(strTbl, 1) = "[" And Right(strTbl, 1) = "]" Then
       strTbl = Mid(strTbl, 2, Len(strTbl) - 2)
   End If
   rst.Filter = "TABLE_TYPE='TABLE' and TABLE_NAME='" & strTbl & "'"
   On Error Resume Next
   blnTableExistsInDB = (UCase(rst.Fields("TABLE_NAME").Value) = UCase(strTbl))
   On Error GoTo 0
   If Err.Number <> 0 Then blnTableExistsInDB = False
   Set rst = Nothing
End Function
Function ExecuteDBQuery(strQuery As String, Optional rngTarget As Range, Optional blnHeader As Boolean) As ADODB.Recordset
   Dim objRecordset                As ADODB.Recordset
   Dim intColIndex                 As Integer
   Dim lngRowOffset                As Long
   On Error GoTo ErrH
   Call blnConnectDB
   If Not rngTarget Is Nothing Then
       Set rngTarget = rngTarget.Cells(1, 1)
   End If
   Set objRecordset = New ADODB.Recordset
   With objRecordset
       .CursorLocation = adUseClient
       '.Open strQuery, g_objConnection, adOpenForwardOnly, adLockReadOnly ', adCmdText
        .Open strQuery, g_objConnection, adOpenDynamic, adLockOptimistic ', adCmdText

       If Not rngTarget Is Nothing Then
           If blnHeader = True Then
               For intColIndex = 0 To objRecordset.Fields.Count - 1 'field names
                    rngTarget.Cells(1, intColIndex + 1).NumberFormat = "@"
                   rngTarget.Cells(1, intColIndex + 1).Value = .Fields(intColIndex).Name
                   rngTarget.Cells(1, intColIndex + 1).Font.Bold = True
               Next intColIndex
               lngRowOffset = 1
           Else  'Without field names
                lngRowOffset = 0
           End If
           If Application.Version < 12 And .RecordCount + rngTarget.Cells(lngRowOffset + 1, 1).Row > 65535 Then
               MsgBox "Records upto row number 65535 can be accommodated. Rest will be ignored.", vbInformation, "Import"
           ElseIf Application.Version >= 12 And objRecordset.RecordCount + rngTarget.Cells(lngRowOffset + 1, 1).Row > 1048576 Then
               MsgBox "Records upto row number 1048576 can be accommodated. Rest will be ignored.", vbInformation, "Import"
           End If
           rngTarget.Cells(lngRowOffset + 1, 1).CopyFromRecordset objRecordset ' the recordset data
        End If
   End With
   Set ExecuteDBQuery = objRecordset
ErrH:
   Set objRecordset = Nothing
   If Err.Number <> 0 Then
       'MsgBox Err.Description, vbCritical, "Error"
        'MsgBox "Database Query Error"
    End If
End Function
Sub DropTable(ParamArray strTableName() As Variant)
   Dim x As Integer
   For x = LBound(strTableName) To UBound(strTableName)
       If blnTableExistsInDB(CStr(strTableName(x))) = True Then
           Call ExecuteDBQuery("Drop Table " & CStr(strTableName(x)))
       End If
   Next x
End Sub
Function blnConnectDB() As Boolean
   Dim blnCon As Boolean
   blnCon = True
   If g_objConnection Is Nothing Then
       blnCon = blnConnectDatabase(gc_strDBPath, "")
   ElseIf Not g_objConnection.State = 1 Then
       blnCon = blnConnectDatabase(gc_strDBPath, "")
   End If
   blnConnectDB = blnCon
End Function
Sub CompactDB()
   Dim lngRes As Long
   Call CloseDB
   lngRes = DatabaseCompact(gc_strDBPath)
   If lngRes = 0 Then
       'MsgBox "Succeeded in compacting database...", vbInformation
    Else
       'MsgBox Error(lngRes)
        Application.StatusBar = "Unable to clean database..."
   End If
End Sub
Function DatabaseCompact(strDBPath As String, Optional strDBPass As String = "") As Long
On Error GoTo ErrFailed
   'Delete the existing temp database
    If Len(Dir$(strDBPath & ".tmp")) Then
       VBA.Kill strDBPath & ".tmp"
   End If
   With CreateObject("JRO.JetEngine")
       If strDBPass = "" Then 'DB without password
            .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ".tmp;Jet OLEDB:Encrypt Database=True"
       Else             'Password protected db
            .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";Jet OLEDB:Database Password=" & strDBPass, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ".tmp;Jet OLEDB:Encrypt Database=True;Jet OLEDB:Database Password=" & strDBPass
       End If
   End With
On Error GoTo 0
   VBA.Kill strDBPath 'Delete the existing database
    Name strDBPath & ".tmp" As strDBPath 'Rename the compacted database
ErrFailed:
   DatabaseCompact = Err.Number
End Function
Sub CloseDB()
   If Not g_objConnection Is Nothing Then
       If g_objConnection.State = 1 Then g_objConnection.Close
   End If
   Set g_objConnection = Nothing
End Sub

Filter/Find in Array

From ExcelExperts.com

Filter/Find in Array:

The following piece of code can be run from any general module. This code extracts all array elements based on the search criteria. You can also search the entire array element or part of it depending on the 3rd parameter of the function if its set to True or False.

Sub TestRun()
   Dim arr
   Dim varSearch
   '-------------Variable parameters----------
    arr = Array(1, 2, 3, 10, 110, 111)
   varSearch = 1
   '==========================================
    MsgBox Join(FilterArray(arr, varSearch, True), vbCr)
End Sub
Function FilterArray(arrVariant, varSearch, Optional blnEntireElement As Boolean = True)
   If blnEntireElement Then
       FilterArray = FilterArrayExact(arrVariant, varSearch)
   Else
       FilterArray = Filter(arrVariant, varSearch, True)
   End If
End Function
Function FilterArrayExact(arrVariant, varSearch)
   Dim arrSearch       As Variant
   Dim arrFiltered     As Variant
   Const strEncl       As String = ":;"
   Const strDelim      As String = ",,"
   'first filter the array for elements containing search variant
    arrFiltered = Filter(arrVariant, varSearch)
   If UBound(arrFiltered) > -1 Then
     'mark the beginning and end of each array element found
      'strEncl and strDelim should not be in the array
      arrSearch = Split(strEncl & Join(arrFiltered, strEncl & _
                     strDelim & strEncl) & strEncl, strDelim)
     'again filter array, include Encl in search
      arrFiltered = Filter(arrSearch, _
                       strEncl & varSearch & strEncl)
     'clear Encl from final result
      FilterArrayExact = Split(Replace(Join(arrFiltered, _
                   strDelim), strEncl, ""), strDelim)
   End If
End Function

Using an Array Formula to Find and Count the Maximum Text Occurrences in a Range

From Chandoo.org

Using an Array Formula to Find and Count the Maximum Text Occurrences in a Range:

A week ago Tarun asked a question on the Chandoo.org Forums.

“I have got multiple names in each row and would like to have what name is repeated maximum number of times and how many times?

Eg. Ram, Amita, Obama, Ram, Willi, Ram, Amita, Chandoo, Ram, Willi

Ans: Ram (4 times)”

(The list and answers are edited)

Chandoo responded with a neat Array Formula:

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0)) &

” (“&MAX(COUNTIF(B2:K2,B2:K2))&” times)”

Lets take a look inside this and see how it works

THE EXAMINATION

The formula has two parts separated by a &

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))

and

&

and

” (“&MAX(COUNTIF(B2:K2,B2:K2))&” times)”

Each part is separate and can be used independently, the & character simply joins the two parts together to make a single string which answers Tarun’s question, Ram (4 times).

Now, lets look at each part.

You can follow along with this forensic examination by downloading the Sample Data File.

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))

This is a single Index Function with 2 components, being:

a Range B2:K2 and

a Count MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0)

Typically an Index Function uses 3 components

=Index(Array, Row Number,[Column Number])

In this example the Range is a single Row, B2:K2

And so using the Counter in the Row spot has the effect of counting down the first Column and then continuing at the top of the second Column etc

So the formula used:

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))

Is equivalent to:

=INDEX(B2:K2,1,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))

Now lets jump ahead to the COUNTIF(B2:K2,B2:K2) bit

If you copy =COUNTIF(B2:K2,B2:K2) to a cell, Press F2 and then evaluate the Formula using F9

You will see that it returns an array. The array is highlighted by the squiggly brackets { } ‘s

={4,2,1,4,2,4,2,1,4,2}

This is the heart of the solution.

What this is showing us is that for each position in the range B2:K2, the count of how many times that cells value occurs in the range B2:K2

So the formula

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))

Is equivalent to

=INDEX(B2:K2,MATCH(MAX({4,2,1,4,2,4,2,1,4,2}), {4,2,1,4,2,4,2,1,4,2},0))

Looking at the MAX({4,2,1,4,2,4,2,1,4,2}) part, this simplifies to 4, the Maximum value of the array (Remember this line, we’ll come back to it later).

So our simplified formula is now: =INDEX(B2:K2,MATCH(4, {4,2,1,4,2,4,2,1,4,2},0))

Now looking at the MATCH(4, {4,2,1,4,2,4,2,1,4,2},0) part of the equation

You can see that Match is looking for the value 4, in the array {4,2,1,4,2,4,2,1,4,2}, which is the First value , Position 1, the 0 requesting that an exact match is found.

So that MATCH(4, {4,2,1,4,2,4,2,1,4,2},0) is equivalent to 1

So our equation =INDEX(B2:K2,MATCH(4, {4,2,1,4,2,4,2,1,4,2},0))

Is now simplified even more to =INDEX(B2:K2, 1)

Index will then look in B2:K2 and will return the first cell or “Ram” in this example.

& “(” & MAX(COUNTIF(B2:K2,B2:K2)) & ” times)”

The second part of the equation is responsible for counting the number of Times Ram occurs and displaying it with some text.

& “(” & MAX(COUNTIF(B2:K2,B2:K2)) & ” times)”

The parts displayed in Red above add the text ( and times) to the Count

Remember the section MAX(COUNTIF(B2:K2,B2:K2)) which was explained above and evaluates to 4 in this case

So the & “(” & MAX(COUNTIF(B2:K2,B2:K2)) & ” times)”

Part evaluates to: ( 4 times)

With the initial & adding it to the text of the first part Ram for the final result – Ram ( 4 times)

LEARN MORE ABOUT ARRAY FORMULAS

You can learn more about Array Formulas at the following links:

http://www.cpearson.com/excel/ArrayFormulas.aspx

http://www.databison.com/index.php/excel-array-formulas-excel-array-formula-syntax-array-constants/

http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

Chandoo.org has several articles on Array Formulas

http://chandoo.org/wp/tag/array-formulas/

FORENSIC FORMULAS

Would you like to see more “Forensic” examination of complex formulas ?

Let us know in the comments below and it may become a regular section at Chandoo.org.