Tag Archives: Excel

Excel: Finding closest value

Question: How to find closest number in a list?

Answer:

Array formula in cell C2:

=INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once
  4. Release all keys

Explaining array formula

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

Step 1 – Return absolute values of numbers in an array, numbers without its sign

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

ABS(number) returns the absolute value of a number, a number without its sign.

ABS(A2:A26-C1)

becomes

ABS({3, 77, 7, 62, 66, 48, 83, 57, 69, 10, 86, 42, 71, 90, 34, 17, 73, 77, 20, 37, 59, 50, 99} – 43)

becomes

ABS{-40, 34, -36, 19, 23, 5, 40, 14, 26, -33, 43, -1, 28, 47, -9, -26, 30, 34, -23, -6, 16, 7, 56})

and returns

{40, 34, 36, 19, 23, 5, 40, 14, 26, 33, 43, 1, 28, 47, 9, 26, 30, 34, 23, 6, 16, 7, 56}

Step 2 – Return the smallest number

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

MIN(number1,[number2]) returns the smallest number in a set of values. Ignores logical values and text

MIN(ABS(A2:A26-C1))

MIN({40, 34, 36, 19, 23, 5, 40, 14, 26, 33, 43, 1, 28, 47, 9, 26, 30, 34, 23, 6, 16, 7, 56}

returns 1.

Step 3 – Return the relative position of an item in an array

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value

MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0)

becomes

MATCH(1, {40, 34, 36, 19, 23, 5, 40, 14, 26, 33, 43, 1, 28, 47, 9, 26, 30, 34, 23, 6, 16, 7, 56}, 0)

returns 12.

Step 4 – Return a value or reference of the cell at the intersection of a particular row and column

INDEX(array,row_num,[column_num]) returns a value or reference of the cell at the intersection of a particular row and column, in a given range

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

becomes

INDEX(A2:A26,12)

and returns 42.

EDIT: Added excel workbook to this blog post

Download excel example file

find-closest-value.xls
(Excel 97-2003 Workbook *.xls)

How to find closest values

Array formula in cell C4:

=INDEX($A$2:$A$24, MATCH(SMALL(ABS($A$2:$A$24-$E$1), ROW(A1)), IF(COUNTIF($D$4:D4, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), “A”), 0))

How to create an array formula

  1. Select cell C4
  2. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  3. Press and hold Ctrl + Shift.
  4. Press Enter once
  5. Release all keys

How to copy array formula

  1. Select cell C4
  2. Copy cell c4 (Ctrl + c)
  3. Select cell range C4:C20
  4. Paste (Ctrl + v)

Download excel file

find-closest-values.xls

How to find closest values and return adjacent values

Array formula in cell D5:

=INDEX($A$2:$A$24, MATCH(SMALL(ABS($A$2:$A$24-$E$1), ROW(A1)), IF(COUNTIF($D$4:D4, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), “A”), 0))

Array formula in cell E5:

=INDEX($B$2:$B$24, MIN(IF((D5=$A$2:$A$24)*(COUNTIFS($D$4:D4, $A$2:$A$24, $E$4:E4, $B$2:$B$24)<COUNTIFS($A$2:$A$24, $A$2:$A$24, $B$2:$B$24, $B$2:$B$24)), MATCH(ROW($A$2:$A$24), ROW($A$2:$A$24)), “A”)))

How to create an array formula

  1. Select cell D5
  2. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  3. Press and hold Ctrl + Shift.
  4. Press Enter once
  5. Release all keys

How to copy array formula

  1. Select cell D5
  2. Copy (Ctrl + c)
  3. Select cell range D6:D28
  4. Paste (Ctrl + v)

Download excel example file

find-closest-values-return-adjacent-value.xlsx

Functions:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ABS(number)
Returns the absolute value of a number, a number without its sign.

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

Advertisements

Excel Functions

auto fill with column names 

=INDEX(Sheet1!$1:$1, ROW())

 

multiple condition lookup

=SUMIFS($I$46:$I$119,$G$46:$G$119,B1,$H$46:$H$119,B2)

 http://chandoo.org/wp/2010/11/02/multi-condition-lookup/

 

CountIf for AND criterias

=SUM((A2:A10=”Cars”)*(B2:B10=”Black”))

http://www.officearticles.com/excel/count_using_multiple_criteria_in_microsoft_excel.htm

Output text files in Excel VBA

Sub LogInformation()
Const LogFileName As String = “D:\TEXTFILE.TXT”
Dim LogMessage As String
LogMessage = “123”
Dim FileNum As Integer
FileNum = FreeFile ‘ next file number
Open LogFileName For Output As #FileNum ‘ creates the file if it doesn’t exist
Print #FileNum, LogMessage ‘ write information at the end of the text file
Close #FileNum ‘ close the file
End Sub

Automate Excel faster

Turn off Screen Updating

Application.ScreenUpdating = False
at the end:
Application.ScreenUpdating = True

Turn On Manual calculation

Sub GoToManual()

Dim xlCalc As XlCalculation

xlCalc = Application.Calculation

Application.Calculation = xlCalculationManual

On Error GoTo CalcBack

‘YOUR CODE

Application.Calculation = xlCalc

Exit Sub

CalcBack:

Application.Calculation = xlCalc

End Sub

Excel Number Formats

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

Open Excel with ArcGIS VBA

To open Excel with ArcGIS VBA,

First, add Microsoft Object Library 12.0 in Tools>Reference.

Then use this code:

‘vba
Dim ExcelApp As Object
On Error Resume Next

Set ExcelApp = GetObject(, “Excel.Application”)

If ExcelApp Is Nothing Then ‘Excel isn’t already open
Set ExcelApp = CreateObject(“Excel.Application”)
End If

ExcelApp.Workbooks.Open (“C:\temp\New Microsoft Office Excel Worksheet.xls”)

ExcelApp.Visible = True

 

ExcelApp.Range(“A1”).value = 100

The much hated Excel Chart Object

For more details, visit here:

http://msdn.microsoft.com/en-us/library/aa213725(v=office.11).aspx

 

When you find that the Xapp.SHEET call in VB.NET hangs, this might be the problem:
What you are trying to call is a CHART OBJECT, not a SHEET, although it looks like one!
Chart objects only have a tab at the bottom of Excel, but they do not contain cells.

To activate a sheet, make this call:
XApp.Charts(Sheetnumber).Activate()

I hope this helps some people out there!

The Complete guide to calling Excel Objects in VB .NET

This guide explains how to use Excel Objects in VB .NET

1. Import Microsoft.Office.Interop

2. Call Excel inside a Try-Catch Statement

Dim XApp As Excel.Application
Dim XBooks As Excel.Workbooks
Dim XBook As Excel.Workbook

Dim XSheet As Excel.Worksheet
Dim XRange As Excel.Range
XApp = CreateObject("Excel.Application")

XApp.Visible = False 'Run Excel in the background

XBooks = XApp.Workbooks
XBook = XBooks.Open(templateXLSX)

XSheet = XApp.Application.Sheets("Raw Data")

3. Manipulate Excel Object

XRange = XApp.Range("A1")

XRange.Value2 = "NEW VALUE"

4. Save the Excel Files

XBook.Close(False) ‘The boolean argument indicates whether the file is saved before closing

5. Clean all Excel objects

Cleaning objects are required, or else the Excel object will not go away in Task Manager

     While (System.Runtime.InteropServices.Marshal.ReleaseComObject(XBook_New) <> 0)
            End While
            XBook = Nothing

            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(XBooks) <> 0)
            End While
            XBooks = Nothing
            XApp.Quit()

            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(XApp) <> 0)
            End While
            XApp = Nothing

For each object, do ReleaseComObject and then set its value to Nothing

If the spreadsheets are updated via a Loop, check carefully that every object is cleaned before it is called again.

For example:

XRange = XApp.Range(“A1″)

XRange.Value2=”NEW VALUE 1”

System.Runtime.InteropServices.Marshal.ReleaseComObject(XRange)

XRange = XApp.Range(“A2”) ‘XRange is cleaned before it is called.

 

Also, call Garbage Collector twice as follows:

       GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()

6. For all language environments to run your program without MUI installed, add this to your code:

   Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
   System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

at the beginning after setting Xapp, and at the end

    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

That’s all!