Excel Examples-1

Some favorite excel formulas. Thank You Industry Giants for your guidance over the years!

Excel conditional format highlight ~* (statistical significance highlighting)

PURPOSE FORMULA EXAMPLE
   
Unique increment ID for each distinct value in a range Use this formula in cell B2 and copy down: =IF(COUNTIF(A$1:A1, A2)=0,MAX(B$1:B1)+1,VLOOKUP(A2,A$1:B1,2,FALSE))   From <https://stackoverflow.com/questions/23400117/unique-increment-id-for-each-distinct-value-in-a-range>  
How to create Correlations in Excel, two columns of data METHOD 1. Use Formula =CORREL(I:I,H:H) METHOD 2. Select Data Analysis (under data/analysis), select correlations, select input range (ctrl+shift+right and down), select output range    
  =COUNTIF(G6:G96,”=1″)
  =IF(ISNA(VLOOKUP(H2,$E$2:$F$70,2,FALSE)),””,VLOOKUP(H2,$E$2:$F$70,2,FALSE))
  =IF(ISNA(VLOOKUP(H2,$E$2:$F$70,2,FALSE)),””,VLOOKUP(H2,$E$2:$F$70,2,FALSE))
  =IF(ISERROR(VLOOKUP($B96,Source!$A:$M,6,FALSE))=TRUE,0,VLOOKUP($B96,Source!$A:$M,6,FALSE))
  =+Trend!W38
  =HLOOKUP(Trend!$A$3,Trend!C:N,ROW())
  =VLOOKUP(C7455,DEALERLISTING_WMS_20070930!Dealer,15,FALSE)
  =SUBTOTAL(9,Q6:Q49983)
# of Distinct Activities {=SUM(1/COUNTIF($F7:$F98,$F7:$F98))}  
Total # of Activities =COUNTA($F7:$F98)
Current plan day

[last place in column]

=LOOKUP(9.99999999999999E+307,C7:C101)

count all that are = F106 value

=COUNTIF($F$7:$F$101,F106) 

count non blank?

=COUNTA(K4:K501)

=DCOUNTA(Issue_DB,5,$A249:$Z250)

=IF(D5=0,””,H5/D5)

=DCOUNT(Issue_DB,32,$D435:$E436)

Grab the Last value in a range if NUMERIC

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

Grab the Last value in a range if TEXT

=INDEX(A:A,MATCH(REPT(“z”,255),A:A)) 

Replace B2 value with value of column 2 in YEARTABLE2 where value in column 1 matches B2

=VLOOKUP(B2,YEARTABLE2,2,0)

=VLOOKUP($E2,CHOOSE({1,2},$A$2:$A$7u0026amp;$B$2:$B$7,$C$2:$C$7),2,0)

From <http://chandoo.org/wp/2014/10/28/multi-condition-vlookup/>

Percent Change

From <http://www.excel-easy.com/examples/percent-change.html>

Custom Number Formats – Billion Dollars Rounded

From <http://www.excelbanter.com/showthread.php?t=176004>

[>=1000000000] $#,##0.0,,, “Billion”;[>=1000000]$#0.0,, “Mil”; $#, “k”

[>=1000000000] $#,##0,,, “Billion”;[>=1000000]$#0.0,, “Mil”; $#, “k”

[>=1000000000] $#.0,, “Billion”;[>=1000000]$#.0,, “Mil”; $#, “k”

will get you “$1.8 Billion”

[>=1000000000] $#,###,, “Million”;[>=1000000]$#.0,, “Mil”; $#, “k”

will get you “$1,839 Million”

[>=1000000000] $#,###,, “Billion”;[>=1000000]$#,, “Mil”; $#, “k”

The above will formatt this $1,965,532 into this $2 Mil

But will also formatt this $1,838,928 into this $2 Mil

[>=1000000000] $#,###,, “Billion”;[>=1000000]$#,, “M”; $#, “K”

=SUMPRODUCT(1/COUNTIF(data,data))

=SUMPRODUCT(1/COUNTIF(D2:D121563,D2:D121563))

[>=1000000] $#,###,,”M”;[<1000000] $#,###,”K”;General

=RIGHT(A2,LEN(A2)-FIND(” “,A2))u0026amp;” “u0026amp;LEFT(A2,FIND(” “,A2)-1)  [reverse word order in excel]

https://exceljet.net/formula/get-month-name-from-date

=TEXT(B4,”mmmm”)  [get month from date, remove 1 m to abbr]

=CHOOSE(MONTH(B4),”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”)

2.82 L/100 km (83.5 mpg?US)[33]

=MID(G2,FIND(“(“,G2)+1,FIND(“mpg”,G2)-FIND(“(“,G2)-1)

GOM67_HON_78

The value in C6 will always start off with: GOM

remove all the characters before the M, including the M

remove after the first underscore and including the first underscore

result should look like this: 67

=MID(C6,FIND(“GOM”,C6)+3,FIND(“_”,C6)-FIND(“GOM”,C6)-3)+0

NESTED IF AND STATEMENT

=IF(Q4>2999;80.54;IF(AND(Q4>1000;QA4<=3000);85.23;IF(AND(Q4>660;QA4<=1000);94.87;89.19)) 

excel Custom Number Formatting T for trillion

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Range(“H:H”)) Is Nothing Then

    If Target.Value < 1000000 Then

      Target.NumberFormat = “#,##0 _M”

    ElseIf Target.Value < 1000000000 Then

      Target.NumberFormat = “#.0,, “”M”””

    ElseIf Target.Value < 1000000000000# Then

      Target.NumberFormat = “#.0,,, _-“”B”””

    Else

      Target.NumberFormat = “#.0,,,, _-“”T”””

    End If

  End If

End Sub

‘ NOTE: The above code is set to monitor Column C… change what I have highlighted in red to the letter designation for the actual column range you want to monitor

‘ HOW TO INSTALL Event Code

‘ If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select “View Code” from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That’s it… the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

‘ http://www.mrexcel.com/forum/excel-questions/691993-bring-abbreviation-b-billion-t-trillion.html

  =SUMPRODUCT(1/COUNTIF(data,data)) =SUMPRODUCT(1/COUNTIF(D2:D121563,D2:D121563)) [>=1000000] $#,###,,”M”;[<1000000] $#,###,”K”;General   =RIGHT(A2,LEN(A2)-FIND(” “,A2))u0026amp;” “u0026amp;LEFT(A2,FIND(” “,A2)-1)  [reverse word order in excel]   https://exceljet.net/formula/get-month-name-from-date =TEXT(B4,”mmmm”)  [get month from date, remove 1 m to abbr] =CHOOSE(MONTH(B4),”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”)
   
count of unique items in a filtered column POWER FORMULA TECHNIQUES by David Hager —How can I find the count of unique items in a filtered column?— Define a column range in your table (excluding header) as Rge. Define unRge as:
=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,””) Then, the array formula to return the # of unique occurrences in a filtered
column is: =SUM(N(IF(ISNA(MATCH(“”,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)
=MATCH(“”,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1))   From <https://web.archive.org/web/20100102204221/http://spreadsheetpage.com/index.php/eee/issue_no_20_july_8_2001/>  

Calculate distance in Microsoft Excel using latitudes and longitudes.

Published on Feb 14, 2012

How to calculate straight line distance between two points in excel using latitudes and longitudes.

The big long formula is:

=6371*ACOS(COS(RADIANS(90-D7))*COS(RADIANS(90-D8))+SIN(RADIANS(90-D7))*SIN(RADIANS(90-D8))*COS(RADIANS(E7-E8)))/1.609

From <https://www.youtube.com/watch?v=bzSnw-Iz6O8>

 


  		 
   
   

How to modify the number of undo levels in Excel

By default, Excel allocates RAM for 16 undo levels. The registry setting that is described in this article lets you change the default value to a value that you specify. 

If the undo history is set to zero, no memory is allocated for undoing actions. The higher the undo history value, the more memory Excel allocates for the history. This directly affects the performance of your computer when you run Excel.
 

  1. In Registry Editor, expand one of the following registry subkeys, as appropriate for the version of Excel that you are running:

    Microsoft Office Excel 2007
    HKEY_CURRENT_USERSoftwareMicrosoftOffice12.0ExcelOptions
    Microsoft Office Excel 2003
    HKEY_CURRENT_USERSoftwareMicrosoftOffice11.0ExcelOptions
    Microsoft Excel 2002
    HKEY_CURRENT_USERSoftwareMicrosoftOffice10.0ExcelOptions
    Microsoft Excel 2000
    HKEY_CURRENT_USERSoftwareMicrosoftOffice9.0ExcelOptions
  2. On the Edit menu, point to New, and then click DWORD Value. Select New Value #1, type UndoHistory, and then press ENTER.
  3. On the Edit menu, click Modify.
  4. In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 100 in the Value box, click OK, and then exit Registry Editor.
  5. Start Excel. Excel stores an undo history for the number of actions that you specified in step 6.

From <https://support.microsoft.com/en-us/help/211922/how-to-modify-the-number-of-undo-levels-in-excel>

count of unique items in a filtered column POWER FORMULA TECHNIQUES by David Hager —How can I find the count of unique items in a filtered column?— Define a column range in your table (excluding header) as Rge. Define unRge as:
=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,””) Then, the array formula to return the # of unique occurrences in a filtered
column is: =SUM(N(IF(ISNA(MATCH(“”,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)
=MATCH(“”,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1))   From <https://web.archive.org/web/20100102204221/http://spreadsheetpage.com/index.php/eee/issue_no_20_july_8_2001/>