Excel Formula for Project Work

Here are some examples of Excel Formula. Highlight the Code with the Mouse and press CTRL+C to copy to the Clipboard. Here is a link to a PDF Document of Excel Functions and here is a link to download an Excel Workbook of Excel Functions with Version Information and Hyperlinks to Microsoft for more information. If you have used any of my work and wish to donate, you can do so on my Donate Page

Here are some different Custom Formats to Format and align numbers with zeros. To use these Custom Formats, right-click->Format Cells and then add one of the Formats below as a Custom Cell Format. You can also use these for Custom Formatting in Pivot Tables where you don't want to display zeros:

' display zero's as wide line, positive & negative numbers (right align by default) #;-#;"—" ' alternative display zero's as wide line, positive & negative numbers (right align by default) 0;-0;—;@ ' display zero's as wide line, positive & negative percentages (%) to 1 decimal place (right align by default) 0%;-0%;"—" ' alternative display zero's as wide line, positive & negative percentages (%) to 1 decimal place (right align by default) 0.0%;-0.0%;—;@ ' display zero's, thousands with comma, positive & negative numbers, right aligns without cell alignment #,##0;-#,##0;"—" ' display zero's, thousands with comma, positive & negative numbers, left align numbers & hyphens (wide line) ' requires you to also left align cells for when you enter zero. the character following the '*' character will be used to pad out the Cell #,##0* ;-#,##0* ;"—" ' hide zero's for number or the return value from a Formula that equals zero but leave Text unchanged 0;;;@ ' generic, negative values formatted to 1 decimal place, wide line General;-#0.0;—;@ ' to mask a Cell to show the Text "Positive Number", "Negative Number" or Zero (0) use the following Custom Cell Format "Positive Number";"Negative Number";0

To display Percentages (%) with +/- & Red Colour Highlighting for negative Percentages you can use one of the following Custom Formats:

' add to C1 with values in A1 & B1 =IF(AND(A1=0,B1>0),1,IF(A1,(B1-A1)/A1,0)) ' add this Custom Format to the Cell C1 +0.0%;[Red]-0.0%;0.0%

To lookup the Last Non-blank Value in a Row and Return the Header (for example a Week Number), assuming that you have the data layout as per below, you can use the following Formula entered into Cell "G2" (you must ensure that your Range does not contain any 0 (zero values) so Find & Replace them with blanks). If using BEx for reporting, you can display zeros as Blank (Headers are in Columns "C1:F1", Data to scan is in Rows "C2:F4"):

=LOOKUP(9.999999999E+307,C2:F2,$C$1:$F$1) ' capture #N/A Error, for example, if you had no Sales data you could display "Never Sold" =IFERROR(LOOKUP(9.999999999E+307,C2:F2,$C$1:$F$1),"Never Sold")

To lookup Numbers that may be stored as Text (there are many examples where data pulled from BEx, BI Reports & SAP extracts leave Numbers stored like this) you can convert the Cells to Numbers or you can use the following methods if you don't want to convert them every time you extract data:

' when you know the width of the Number ie. it will always be 8 digits, you can pad to 8 digits like this =VLOOKUP(Text(A1,"00000000"), ... ' coerse to Integer =VLOOKUP(Int(A1), =VLOOKUP(VALUE(A1), =VLOOKUP(NUMBERVALUE(A1), =VLOOKUP(T(A1) ... ' I prefer this one as it does not matter what length the Number may be =VLOOKUP(A1&"", ... ' another method is to use a number format like this VLOOKUP(TEXT($B9,"00#"), ... ' use this when using an Index, Match Formula on data stored as Text when trying to find a Number =INDEX(C:C,MATCH(A1&"",B:B,0))

There are a few ways to convert Numbers stored as Text using Formula. You can use =VALUE() or =NUMBERVALUE() for floating point Numbers. You can even use =INT() if you just have whole Numbers or =T() to retain formatting. If you have a Number stored as Text in Cell A1 use any of the following:

' converts a Number stored as Text into a Number =VALUE(A1) ' whole Number =INT(A1) ' Converts text to a number, in a locale-independent way (new for Excel 2013) =NUMBERVALUE(A1) ' if you have a Number stored as Text like 1,200 this will retain its comma formatting =T(A1)

To SUM Numbers that may be stored as Text (assuming your Range to SUM is 'A1:C1') you can use an Array Formula with double negative signs like this (the curly braces will appear when you press CTRL+SHIFT+ENTER). This also works well when adding Ranges containing a Custom Cell Format ie. a wide line '—' (dash):

' enter this in D1 =SUM(--(A1:C1)) ' this will look like the following in the Cell when you press CTRL+SHIFT+ENTER {=SUM(--(A1:C1))}

Another method to SUM Numbers where a Range has a '—' (dash) in it (assuming your Range to SUM is 'A1:C1') and you are using the Custom Format #,##0;-#,##0;"—", is to use an Array Formula together with the SUBSTITUTE Function like this (it works by substituting all occurances of '—' with zero (0) so you get {"4,321","0","997"} in the Array instead of {"4,321","—","997"}):

' enter this in D1 =SUM(--SUBSTITUTE(A1:C1,"—",0)) ' this will look like the following in the Cell when you press CTRL+SHIFT+ENTER {=SUM(--SUBSTITUTE(A1:C1,"—",0))}

To lookup an ISO Week Number using a Calendar Day Date use the following methods depending on your Excel Version:

' enter a Date into Cell "A1" 30/03/2015 ' either enter this formula into Cell "A2" =ISOWEEKNUM(A1) ' ... or, enter this formula into Cell "A2" ="ISO Week "&INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7) ' results of the formula either 14 or ISO Week 14

To lookup the Gender from 3 Columns of data Name, Age & Gender Columns "A2:C5" with "A1", "B1" and "C1" being the Headers, "E2:E3" being the 2 Search Criteria, you can use the following Array Formula (SHIFT+CTRL+ENTER) in "F2" - Alternatively download the Example Multiple Lookups Using Array Formula.xlsx which features 2 Examples of using Multiple Values to return data:

' lookup the Gender from 3 Columns of data Name, Age & Gender Columns "A2:C5" =INDEX(C2:C5,MATCH(E2&E3,(A2:A5)&(B2:B5),0),1)

To count the unique values in a large Range I find this is one of the quickest Formula - remember to enter the Formula as an Array Formula (SHIFT+CTRL+ENTER). The keyword Range should be a Range i.e. "A6:A5000" stored as a Defined Name (or Named Range) and should be replaced when you enter it. For very large Ranges or entire Columns you should use VBA:

' you need a Defined Name called Range. remember to enter this as an Array Formula by pressing CTRL+SHIFT+ENTER =SUM(IF(FREQUENCY(IF(range<>"",MATCH(range,range,0)),ROW(range)-MIN(ROW(range))),1))

Using a dynamic Defined Name or Named Range as a data source for a Pivot Table enables you to Paste in any size of data and then refresh the Pivot Table without re-defining the data source Range for the Pivot Table. Providing the same Headers are included the Pivot Table can be refreshed for the new Range. If you include additional Columns with new Headers the Pivot Table will accept these as well

I make the assumption that you already have some source data and a Pivot Table linked to the Range. So, in the Worksheet containing your Pivot Table source data with Headers, create a Defined Name called Data using the Ribbon Tab "Formula" and the Name Manager. Then add the following Formula:

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Click OK. Close the Name Manager. Re-open the Name Manager and you will see that the Defined Name has taken the name of the Worksheet in its Formula:

=OFFSET(data!$A$1,0,0,COUNTA(data!$A:$A),COUNTA(data!$1:$1))

Now simply use the Defined Name "Data" as the Range for the Pivot Table. Click on the Pivot Table. On the Ribbon in "PIVOTTABLETOOLS" context Menu, click on "ANALYZE" Group and then select the "Change Data Source" Control. Underneath the Heading "Choose the data that you want to analyze" and for the Radio Button option "Select a Table or range" enter the following:

=Data

Click OK. Now right-click and refresh your Pivot Table. No changes will occur. Now Paste in a new Range to include the existing Headers but either longer Rows or additional Columns and right-click refresh again on your Pivot Table. The Pivot Table data source will have expanded and the Pivot Table will include the new data

Here is an example of using a dynamic Named Range to capture a Range when zeros (0) are included. It always captures the last Row down of the last Numeric Cell entry NOT including zero (0). The Cells in the Range are also formatted with a '—'. The Range starts in Cell 'D7' on Sheet1. Try adjusting the Percentages to zero - when you check the Defined Name you will see that it still captures the Range correctly, including any Blank Columns if Columns after the Blank Column have values. To see this in action, download Dynamic Named Ranges.xlsx. View the Defined Name to see how it picks up the data. The Formula in this example is shown below:

=OFFSET(Sheet1!$D$7,0,0,COUNTA(Sheet1!$D$7:$D$99988),LOOKUP(9.99E+307,1/(Sheet1!$D$7:$O$7),COLUMN(Sheet1!$D$7:$O$7)-3))

Here is a Formula that you can use to capture a list of names including Blank entries in between Rows. The beauty of this Formula is that it ignores the Blank Names but still captures the entire list of Text (or Range required). On a Worksheet called "Summary", enter data in Column "B", your Header should be in Cell "B5" and then the list of Names should be entered below the Header. View the Defined Name to see how it picks up the data that you enter. The Formula to do this is shown below:

=OFFSET(Summary!$B$6,0,0,MATCH(9.99999999999999E+307,Summary!$B:$B,1)-5, 1)

Here is an example of how to implement a Top Ten ranking linst for Supplier Number and Supplier Name together with the Percentage that they achieved. You can choose how many rankings you wish to view. If the percentages are the same they will still be given an order of ranking. You can download the Top Ten List.xlsx Workbook to see how I have done it. The Formula used to create the ranking is shown below:

=C1+10^-6*ROWS($A1:A$1)

Here is an example of how to implement R1C1 Lock Formula in VBA for an Excel Formula. Locking Formula when using R1C1 (normally used after recording a Macro to do something in Excel) can be a little tricky especially understanding when it is absolute or relative and then trying to adjust the Code to Lock a Row or a Column when your Code is trying to Copy & Paste or just Copy down the Formula. here is the R1C1 Lock Formula.xlsm Workbook:

Here is a method to repeat the values in Column 'A', (n) number of times required in Column 'B' outputting the results in Column 'C'. The Formula is an Array Formula requiring you to press CTRL+SHIFT+ENTER. So with a list in Column A and the respective 'X' times to repeat the elements of the list in Column 'B', enter the following Array Formula into Column 'C'. You will need to extend the Ranges if you want to add more Products to the list. Here is the Repeat Value X Times.xlsx Workbook:

' enter the following array formula in Cell C2 =IFERROR(INDEX($A$2:$A$5,MATCH(TRUE,MMULT(--(ROW($A$2:$A$5)>=TRANSPOSE(ROW($A$2:$A$5))),$B$2:$B$5)>=ROWS($1:1),0)),"") ' this will look like the following in the Cell when you press CTRL+SHIFT+ENTER {=IFERROR(INDEX($A$2:$A$5,MATCH(TRUE,MMULT(--(ROW($A$2:$A$5)>=TRANSPOSE(ROW($A$2:$A$5))),$B$2:$B$5)>=ROWS($1:1),0)),"")}

Here is an example of how to create a unique list of text items in Column 'B' from a list of multiple items in Column 'A' using Excel Formula. Enter some text items in Column 'A' including a Header (I used Product 1, Product 2, etc.) and then Copy & Paste the Array Formula below into Cell 'B2' and drag down. You will see a unique list built from the items that you entered in Column 'A'. The result of your list should also be Sorted ascending. Please note it is Case Insensitive, so 'a' is treated the same as 'A' and therefore will only be repeated once. Here is the Unique List Formula.xlsx Workbook:

' enter as an array formula, CTRL+SHIFT+ENTER in Cell B2 for a list of Items in Column A ' drag the Formula down to create the unique list =IFERROR(INDEX($A$2:$A$20,MATCH(SUM(COUNTIF($A$2:$A$20,B$1:B1)),COUNTIF($A$2:$A$20,"<" &$A$2:$A$20),0)),"")

These Formula will give you the Average or SUM of the Top 10 Numbers in a Row for the Range "C9:V9". Change the Array to 11 to use the Top 11 Numbers. Here is the AVG Top 10 Numbers.xlsx Workbook:

' Average Top 10, enter as an array formula, CTRL+SHIFT+ENTER =AVERAGE(LARGE(C8:V8,{1,2,3,4,5,6,7,8,9,10})) ' SUM Top 10, enter as an array formula, CTRL+SHIFT+ENTER =SUM(LARGE(C9:V9,{1,2,3,4,5,6,7,8,9,10}))

Suppose you wanted to consider a Formula to tell you if a Number was larger than zero, less than zero or exactly equal to zero, you can use many different methods to arrive at what you need to do. Here are some methods of switching on an inter value. Here is the Boolean Switch.xlsx Workbook:

' 0,1,2 Boolean - notice that this only does 2 tests! =(A9>0)*1+(A9<0)*2 ' Standard OR =OR(A9>0,A9<0) ' CHOOSE (3-way decision) - notice that this only does 2 tests! =CHOOSE(((A9>0)*1+(A9<0)*2)+1,"Equal to zero","Larger than zero","Less than zero") ' Standard IF =IF(OR(A9>0,A9<0),"Within Parameters","Outside of Parameters")

Here are two Array Formula to SUM across Columns by some Criteria and to SUM across Columns matching Criteria whilst also matching Criteria in a Row. A list of Products are detailed in Column 'A' together with repeated Column Headers, "A, B, A, D, D". Select a Column Header in Cell 'B9' from the first Drop-down to SUM that Column Header. Select a Column Header in Cell 'B14' from the first Drop-down and a Product from the second Drop-down in Cell 'B15' to SUM the Products across all repeated Columns for the Column Header. You can modify these Formula to suit your own needs. Here is the SUM Columns by Column Criteria & Row Criteria.xlsx Workbook:

' formula for example 1, enter as an array formula, CTRL+SHIFT+ENTER =SUM(IF($B$1:$F$1=$B$9,$B$2:$F$4)) ' formula for example 2, enter as an array formula, CTRL+SHIFT+ENTER =SUM(INDEX(IF($B$1:$F$1=$B$14,$B$2:$F$4),MATCH($B$15,$A$2:$A$4,0)))

Here is a quick way of reversing a List using Formula. For a list in Cells "A1:A5", enter the following in Cell "B1" and drag down to "B5". You **must** have items all of the way down from "A1:A5" otherwise the Formula will places zeros for empty Cells. The list will now be reversed. To extend this Formula, amend '6-ROWS' to the number of Rows + 1 ie. for 10 Rows the Formula will be '11-ROWS' and the Range wll be "$A$1:$A$10":

' for a list of items in Cells "A1:A5" enter this formula in Cell "B1" & drag down to reverse the list =INDEX($A$1:$A$5,6-ROWS($A$1:A1))

Here is a quick way to Sort a Text List Alphabetically. For a list in Cells B2:B5, enter the following =COUNTIF in Cell "A2" and drag down to "A5". Then enter the =VLOOKUP to sort the Items. Please Note: if you have duplicate Text, the Formula will fail:

' for a list in Cells "B2:B5" enter this formula in Cell "A2" =COUNTIF($B$2:$B$5,"<="&B2) ' for a list in Cells "B2:B5" enter this formula in Cell "C2" =VLOOKUP(ROW()-ROW($B$1),$A$2:$B$5,2,FALSE)

Here are a couple of quick ways to apply a Formula to Colour Band your report data starting at Row Y & Banding every Y Rows or by Criteria. To Colour Band report Data every other Row using Formula, select the entire data Range in your Excel Workbook that you want to Colour Band and add a Conditional Format from the "Styles" Group on the "Home" Tab (Manage Rules->New Rule->Use a formula to determine which cells to format) using the Formula below (I selected "B6:D105"). Change the X in the Formula to the Row Down number that the Selected Range begins (I chose to enter 7 so that the Colour Banding was applied after the first Row within my Selected Range). Change the Y in the Formula to the number of Rows you want to highlight (so for every other Row change it to the number 2). Choose your desired Fill Formatting. Here is the Colour-Banding.xls Workbook:

' Example 1, Formula for the example above =MOD(ROW()-7,1*2)+1<=1 ' Formula to copy - modify the X & Y for your own selected report Range =MOD(ROW()-X,1*Y)+1<=1 ' Example 2, Formula to start at Row 10 and Colour Band every 5 Rows =MOD(ROW()-10,1*5)+1<=1

To Colour Band Report Data by Criteria as Formula, enter some Names in a Blank Worksheet from Cell "C6" downwards. Include the Name "Mark" in Cell "C11" and Cell "C13". Select the entire Range of entered data and add a Conditional Format from the "Styles" Group on the "Home" Tab (Manage Rules->New Rule->Use a formula to determine which cells to format) using the Formula below. Click Format and select a Fill Colour. Then Click OK and then Apply. The two Cells with the Text "Mark" will be Colour Banded:

=$C6="Mark"

You can also download Colour Banding with Hyperlink Rollover.xlsm for Excel 2007/2010 by Bert van Zandbergen to see a Rollover technique in action

Here are a couple of ways to remove whitespace from Cells (extra spaces) using Trim() and Substitute(). Copy or enter the following into Cell "A1" without the quotes: " 5 600 ". Example 1 will remove the leading & trailing whitespace. Example 2 will remove all whitespace. Example 3 will remove all whitespace and format the Cell to a number using the Text() function (Please note: you will then still have to Convert the Cell to a number using the little drop-down arrow):

' Example 1 - remove the leading & trailing whitespace =TRIM(A1) ' Example 2 - remove all whitespace =SUBSTITUTE(A1," ","") ' Example 3 - remove all whitespace and format the Cell to a number =TEXT(SUBSTITUTE(A1," ",""),"#,##0")

Here are a couple of ways to SUMIF across Columns of data to exclude some Criteria - you need to download the accompanying File to see the Formula in action. 4 Columns of data from "A1:D7" including 'Product', 'Product Description', 'Depot' and 'Stock' are used for the Examples. Here is the SUMIFS to Exclude Criteria.xlsx Workbook:

' Example 1 - SUMIF Column 'D' but exclude Products containing the letter 'C' in Column 'A' =SUMIFS($D$2:$D$7,$A$2:$A$7,"<>*C*") ' Example 2 - SUMIF Column 'D' but exclude Products containing the letter 'C' in Column 'A' & Depots including 'Depot A' in Column 'C' =SUMIFS($D$2:$D$7,$A$2:$A$7,"<>*C*",$C$2:$C$7,"<>Depot A")

Here are a couple of ways to get the number of days in a Month given a Date in Cell "A1":

' Example 1 - add Formula to Cell "A2" =DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1) ' Example 2 - add Formula to Cell "A2" =DAY(EOMONTH(A1,0))

Here are a couple of ways to calculate the Date Difference between two Dates and will calculate your age in years and then Years, Months & Days:

' Example 1 - Years =DATEDIF("25-Jul-1968", TODAY(),"Y") ' Years... for a Date in Cell "A1" =DATEDIF(A1, TODAY(),"Y") ' Example 2 - Years, Months & Days =DATEDIF("25-Jul-1968", TODAY(),"Y") & " Years " & DATEDIF("25-Jul-1968", TODAY(),"YM") & " Months " & DATEDIF("25-Jul-1968", TODAY(),"MD") & " Days" ' Years, Months & Days... for a Date in Cell "A1" =DATEDIF(A1, TODAY(),"Y") & " Years " & DATEDIF(A1, TODAY(),"YM") & " Months " & DATEDIF(A1, TODAY(),"MD") & " Days"

Here is a way to perform a backwards VLOOKUP using the CHOOSE Function. Consider a list of Products in Cells "A2:A5" (Product A, Product B... etc.) and a list of Depots in Cells "B2:B5" (Depot A, Depot B... etc.) then a VLOOKUP on the first occurance of "Depot B" will return "Product B" using the formula below. Here is the Backwards VLOOKUP using CHOOSE.xlsx Workbook:

' Example, returns the first Product for a Match on "Depot B" =VLOOKUP("Depot B",CHOOSE({1,2},$B$2:$B$5,$A$2:$A$5),2,0)

Here is a way to SUM by Criteria in a Pivot Table which also has a Custom Cell Format: #,##0;-#,##0;"—". The Range for the Criteria is D27:D1002 and the Range to SUM is E27:BE1002 in the Pivot Table - this is actually outside of the Pivot Table data being displayed but the great thing about Pivots is that they expand and contract the Range allowing me to use this Fornula to capture any horizon of Final Forecast and Total Sales ie. 1 Week or 16 Weeks:

' Examples =SUMPRODUCT(($D$27:$D$1002="Final Forecast")*$E$27:$BE$1002) =SUMPRODUCT(($D$27:$D$1002="Total Sales")*$E$27:$BE$1002)

Here is a way to SUM a Column until you hit the first Blank Cell. With a Header in "B1" and data from "B2" until "B13" add the following Array Formula in Cell "B14" remembering to press CTRL+ENTER:

' Formula =SUM(B2:INDEX(B2:B13,MATCH(TRUE,(B2:B13=""),0))) ' this will look like the following in the Cell when you press CTRL+SHIFT+ENTER {=SUM(B2:INDEX(B2:B13,MATCH(TRUE,(B2:B13=""),0)))}

Year on Year - YoY Comparison for Same Periods. Here is a method to SUM current Sales data against last years Sales data for the same periods up until the last current Sales entry, open and view the Formula in the following SUM Values in Columns until a Blank Cell.xlsx Workbook

Here are two methods to Copy Rows down where you have a Column that does not automatically spawn data to each Row. Method 1 (Formula) - In the example Formula below the first data is in Column "B9", add your Formula to Cell "A9" then Copy down. After this Copy & Paste back over the Range in Column "B":

' Formula =IF(B9<>"",B9,A8)

Method 2 (using Fill Down & Goto Special) - Select the range that contains blank cells you need to fill starting with the first Blank Cell underneath the first item in the Range ie. "A3". Press "F5" or Click Home > Find & Select > Go To Specialâ€¦ and a Go To Special dialog box will appear, then check Blanks option. Click OK, and all of the blank cells have been selected. Then input the formula "=A2" into the Formula Bar. This cell reference can be changed as you need. Press Ctrl + Enter and Excel will copy the respective formula to all blank cells

Here is a way to increment a MOD pattern by a variable to create a positive varying pattern. This can be used in the INDEX() function to return a specific value in a list. Here is the Formula - Copy & Paste this into a Cell below "A1" and replace "X" with a number like 1,2,3 etc. or use a Cell or Defined Name that changes to create your pattern - drag the Cell down to create the MOD pattern:

' replace 'X' with a number of your choice and drag the Cell down =MOD(ROWS(A$1:A1)-1,X)+1 ' create a pattern like 1,2,3,4 by entering the Formula below and dragging it down =MOD(ROWS(A$1:A1)-1,4)+1 ' create a pattern like 1,2,3,4 across Columns instead of Rows =MOD(COLUMNS($A:A)-1,4)+1 ' create a normal pattern to increment by 1 every N Rows ie. 5 Rows, drag down =INT((ROW(A1)-1)/5)+1 ' create a normal pattern to increment by 1 every N Columns ie. 5 Columns, drag across =INT((COLUMN(A1)-1)/5)+1 ' create a repeating pattern across Columns like 1,2,3,4,5,1,2,3,4,5 - doesn't allow zero (0) =MOD(COLUMN(A:A)-1,5)+1 ' create a repeating pattern down Rows like 1,2,3,4,5,1,2,3,4,5 - doesn't allow zero (0) =MOD(ROW(1:1)-1,5)+1 ' auto-increment after 11 Rows, enter into Cell "A2" and drag down. change the '11' to the number of Rows before increment =IF(MOD(ROWS($A$1:A1),11)=0,A1+1,A1) ' auto-increment after 11 Rows, enter this one in Cell "A1" and drag down. change the '11' to the number of Rows before increment =INT(((ROW(A1)-1)/11))*1+1 ' auto-increment after 3 Rows, enter into Cell "A1" and drag down. change the '3' to the number of Rows before increment =-INT(-ROWS(A$1:A1)/3) ' starting with the number 5 in Cell"A1", this pattern will auto-increment every 6th Column by 3, ie. if Days Monday to Friday = 8 to 12, the next Monday will be 15 through to 19 =IF(MOD(COLUMN(A:A)-1,5)+1=1,A1+3,A1+1)

If you replace "X" with 2, you can use it to return (or repeatedly return) the 1st and then 2nd item in a list or Range ie. to return the 1st and 2nd item in the Range "$A$1:$A$4" you can use the following Formula:

' build a pattern by continually returning the first and second Item in a list in the Range "A1:A4" =INDEX($A$1:$A$4,MOD(ROWS(A$1:A1)-1,2)+1)

The above Formula will make the pattern repeat for the length of Rows that you drag it down ie. if the Formula returned "Fish" and "Milk", then dragging it down a number of Rows will give you "Fish","Milk","Fish","Milk", etc.

This Formula will SUM the Cells from a Range every 3rd Row (adjustable by changing the 3):

' replace '3' with a number of your choice. press CTRL+SHIFT+ENTER to enter the Formula with data in the Range "A1:A9" =SUM(A1:A9*(MOD(ROW(A1:A9),3)=0))

Here is a SUM Numbers Every 3rd Row.xlsx Workbook detailing way to SUM numbers every 3rd Row - the Formula in Column B just demonstates the way MOD works every 3rd Row

Here is a way to Multiple lots of Cells by a Number like 100:

1. Type 100 in a blank Cell

2. Copy the Cell

3. Select the Cells that you want to Multiply by 100

4. Right-click, Paste Special, Operation Multiply

5. Remove the original 100 Number from the blank Cell

You can do the same for Addition or Subtraction and use a different Number in the Cell that you Copy

Here is a way to add the same Value or Character to lots of Cells at the same time:

1. Highlight the Range of Cells that you want to add the Value or Character into

2. Type the Value or Character ie. a

3. Press CTRL+ENTER (Control plus the Enter Button) at the same time

I have read a lot on this Subject and can't help thinking everybody is tackling it the wrong way! To prevent the word (Blank) from being in your Pivot Table simply add the character (') without the Brackets to any Blank Cells in your SOURCE DATA. That's all there is to it, refresh your Pivot Table and all of the (Blanks) will vanish:

To Capitalise or make the first character of each Word uppercase you can use the PROPER Function. With the String "the cat sat oN the mAT" in Cell "A1" use the following Formula in "A2". The result will be "The Cat Sat On The Mat":

' this Formula will Capitalise a String in Cell "A1" =PROPER(A1)

The Formula below inserts currency symbols, using the CHAR function. The result will be "Exchange Rates Chart (£), Euro(€) and Dollar ($)":

="Exchange Rates Chart (" & CHAR(163) & "), Euro (" & CHAR(128) & ") and Dollar (" & (CHAR(36) & ")")

The Formula below can be used where you have a Row of Data (starting H9 through to BH9 - data may not be up to the last Cell in this Range but this will be the maximum that it ever will be) and you want to SUM or AVERAGE the last 12 Cells of data from the end of the Row data backwards - if the 12 is substituted for a selected number 1-X, then your Formula will become dynamic, counting backwards 12 Columns and allowing the calculation to be 12 Columns wide:

' SUM Formula, last Column of data, 12 Columns Back =SUM(OFFSET($H9,,COUNTA($H9:BH9)-12,,12)) ' Average Formula, last Column of data, 12 Columns Back =AVERAGE(OFFSET($H9,,COUNTA($H9:BH9)-12,,12))

The Formula below can be used where you want to get the Average dynamically. Suppose you have a report with data in weeks across the page and you want to specify the week to look at and get the last 4, 8 or 12 Weeks Average from that week - this Formula will do just that. Here is the Average of X Weeks from Weekly Columns.xlsx Workbook:

' where H9:BH9 are the weeks, H6:BH6 are the actual numbers of the weeks, where F5 is the current week, F6 is the number of weeks back to Average (see the File below for an Example) =AVERAGEIFS(H9:BH9,H$6:BH$6,">"&$F$5-$F$6,H$6:BH$6,"<="&$F$5)

The Formula below can be used to identify duplicates in a list. Then you can simply Filter the lists to view the duplicates, Copy and Paste them out or Delete them from the list. If you want to find the duplicates across 2 Columns (ie. Product & Depot duplicates), concatenate the Column values into another Column and then use the same Formula on the concatenated Column. Here is the Identify Duplicates in a List using Formula.xlsx Workbook:

' where the list starts in A6 add this Formula to B6 - drag down for as many items in your list =IF(COUNTIF($A$6:$A6, A6)=1,0,1)

The Pattern below can be used to Find & Replace Negative Floating Point Numbers in Excel. Simply press CTRL+F and using Replace enter '-*' or '-*.*' without the apostrophe's and what you want to replace the contents with ie. leave Blank for Nothing. You can also press the 'Find All' Button after using CRTL+F to display all of the negative values found and their Cell references):

' find all negative floating point numbers (locates the largest numbers first if you press Find All after CTRL+F) -* ' find all negative floating point numbers alternative (locates the smallest numbers first if you press Find All after CTRL+F) -*.* ' to find values like -0.01 you could use -*.0*

Method 1: To get the Sheet Name from a Tab using a Formula you can use the following (Please note: the Workbook must be saved first):

=RIGHT(CELL("Filename",A1),LEN(CELL("Filename",A1))-FIND("]",CELL("Filename",A1)))

Method 2: My preferred method which allows you to switch Workbooks without getting a #Value error:

' add the Formula below to Cell "A1" =CELL("filename",A1) ' add the Formula below to Cell "A2" ="'"&MID(A1,FIND("]",A1)+1,256)&"'!" ' RESULT for a Workbook called Dynamic Chart.xlsx and a Worksheet Tab called Dynamic Chart in A1: C:\Archive\- a_My Programming\- a_My Excel\Working\Dynamic Chart\[Dynamic Chart.xlsx]Dynamic Chart ' RESULT for a Tab called Dynamic Chart in A2: Dynamic Chart'!

Sometimes when bringing in BI Data, values will only display as an Asterix '*'. This is because of the way that BEx aggregates values across different Units of Measure / Pack Types etc. It cannot display 'HL' or 'PC' if Products are different it displays a default Asterix. This makes it difficult to SUM or even reference the values. You can remove the Formatting in BEx and all will work well, however you can also use 'TEXT' or '&0' to SUM or reference the values. To reference a value and coerse it into a Custom Format like '1,116.615 HL' you can use the following:

' the Formula =TEXT(A1,"#,##0.000 ""HL""") ' the Custom Format used in the Formula above #,##0.000 "HL"

You can also use the following to SUM data - bear in mind it will not have an underlying Format and will be up to 14 significant places:

=SUM(A1:C3)&0

Sometimes you may wish to sort data in a Column like this: '01.2015, 02.2015, 01.2016' which would sort incorrectly like this '01.2015, 01.2016, 02.2015'. To reverse or Sort a Column of Data that Cannot usually be Sorted do the following:

1. Add a column next to the column you'd like to flip, reverse or sort

2. Fill that column with numbers, starting with 1 in the first Row, then 2 in the next Row and dragging down to the number of rows in the column you'd like to reverse

3. Select both columns and click Data > Sort

4. Sort by the second Column Ascending or Descending

To get the Address of the Last Cell in a Range of Columns you can use the following Array Formula (entered by pressing CTRL+SHIFT+ENTER). Add some data to Row 1 for as many Columns as you like and add the Formula below to A2:

' add this Formula to Cell "A2" and press CTRL+SHIFT+ENTER =CELL("address",INDEX($1:$1,MAX(($1:$1<>"")*(COLUMN($1:$1)))))

I entered some data up to Column E in Row 1, therefore I got the following result from the Formula which includes any Blank Cells:

$E$1

To get the Column Number of the Last Cell in a Range of Columns you can use the following Array Formula (entered by pressing CTRL+SHIFT+ENTER). Add some data to Row 1 for as many Columns as you like and add the Formula below to A2:

' add this Formula to Cell "A2" and press CTRL+SHIFT+ENTER =MAX((1:1<>"")*COLUMN(1:1))

I entered some data up to Column E in Row 1, therefore I got the following result from the Formula which includes any Blank Cells:

5

To get the Row Number of Last Cell in a Range for Column 'A' enter the following Match Formula in Cell "B1":

' enter the Formula below into Cell "B1". the data must be Numeric or a Date ie. 5,6,7 or 01/04/2017 =MATCH(1,A:A,-1)

To get the Date from a Week Number given 53.2015, 01.2016, 03.2017 etc. with the result being a Monday Date, you can use the following Formula - ensure that your dates are in Row 2, Column A, B, C etc. and Paste this Formula into Cell "A1", then drag right:

' enter some ISO Week Numbers in the Format of 53.2015, 01.2016, 03.2017 etc. add the Formula below to Cell "A1" & drag right... =DATE(INT(RIGHT(A2,4)), 1, -2) - WEEKDAY(DATE(INT(RIGHT(A2,4)), 1, 3)) + INT(LEFT(A2,2)) * 7 ' or for a Date in Cell "A1" =ISOWEEKNUM(A1)

There are a few ways to average numbers and ignore zero which may give a different result than a simple =AVERAGE() Formula:

' normal average including zero's - A1:D1 contains 1, 0, 3, 0 & equals '1' =AVERAGE(A1:D1) ' method 1 to ignore zero - A1:D1 contains 1, 0, 3, 0 & equals '2' =AVERAGEIF(A1:D1,"<>0") ' method 2 to ignore zero - A1:D1 contains 1, 0, 3, 0 & equals '2' (array Formula entered pressing CTRL+SHIFT+ENTER) {=AVERAGE(0+A1:D1)}

Index, Match can be used instead of VLOOKUP as it is faster and can reference entire Columns without changing Formula. Consider the following data, 2 Columns with Name and Age in Column A and Column B respectively. With a list of different Names in Column D, add the following Formula to Cell "E2":

' simple Index, Match Formula =INDEX(B:B,MATCH(D2,A:A,0)) ' means Index the Column that contains the value that you want to bring back =INDEX(B:B ' means match the value in Cell "D2" - this is the lookup value ,MATCH(D2 ' means match the value in Cell "D2" to a Column containing a list of like values ,A:A ' means make an exact match ,0))

If you have a Cell that contains 2 items separated by a Slash ie. '12345 / TaittingerMosaicNocRse BOT 6X0,75' and you want to extract either the first item or the last item, in this instance the 'Product Code', '12345' and the 'Product Description', 'TaittingerMosaicNocRse BOT 6X0,75', then you can use the Code below or download the File at the bottom of the article to see how it's done and/or adjust for other items/separators. Here is the Extract Product Code & Description.xlsx (9.49 KB) Workbook:

' Cell Text in Cell "C2" 12345 / TaittingerMosaicNocRse BOT 6X0,75 ' Product (left part) - assumes the Cell to extract is in "C2" add this Formula to Cell "A2" =INT(TRIM(LEFT(C2,FIND("/",C2,1)-1))) ' Product Description (right part) - assumes the Cell to extract is in "C2" add this Formula to Cell "B2" =TRIM(RIGHT(C2,LEN(C2)-FIND("/",C2,1)))

Here is a replacement for the =IF Function. We will use a Boolean result of a number of tests as the 'Index'+1 in and pass this into a =CHOOSE Function. The beauty of this is that we don't even have to test for a zero (0) Index as this will be an Index of 1 automatically. Also we can write any other Function into any of the Index slots

Every time one of our tests is correct a multiplier by 1, 2, 3 etc. will give a new Index that we can pass into the =CHOOSE Function. So in this simple example, we test for a positive number, a negative number or zero (0) and react accordingly all wrapped up in a neat little Formula. Here is the Replacement for IF.xlsx (9.39 KB) Workbook. Here is the Boolean Switch.xlsx (12.1 KB) Workbook

' will test for a positive number, a negative number and if the result is zero (0) will display a zero result as a zero (0) =CHOOSE(((A3+B3>0)*1+(A3+B3<0)*2)+1,0,A3+B3,A3+B3*-1) ' will test for a positive number, a negative number and if the result is zero (0) will display a zero result as Nothing ie. "", Blank =CHOOSE(((A3+B3>0)*1+(A3+B3<0)*2)+1,"",A3+B3,A3+B3*-1)

Here are some methods to get the last Row number of data in Column "B" containing Numeric or Text with or without Headers. You can then use Index to return the Contents of the Row:

' these Formula must have Numeric and Text somewhere in the Column to Work (you can add 1 to a Row and hide the Row if your data does not contain both by default) =MAX(MATCH(1E+306,Summary!$B:$B,1),MATCH("*",Summary!$B:$B,-1)) =MAX(MATCH(9.99999999999999E+307,B:B),MATCH(REPT("z",255),B:B)) ' these Formula are Array Formula and do not require both Text and Numeric data to be present but will return the last Row ' - remember you can use these Formula in Defined Names as they are evaluated ' - Copy & Paste without the curly braces {=MAX(ROW(B:B)*(B:B<>""))} {=MAX(IF(NOT(ISBLANK(B:B)),ROW(B:B)))} {=MATCH(9.99999999999999E+307,1/(1-ISBLANK(B3:INDEX(B:B,ROWS(B:B)))))+ROW(B3)-1+ISBLANK(B3:INDEX(B:B,ROWS(B:B)))} ' this Formula will bring back the Cell entry in the last Row again Text or Numeric =LOOKUP(2,1/(LEN(B:B)>0),B:B)

Here is a Formula to parse out last occurance of 'some Text' from a Cell containing the '/' Forward Slash Delimiter. The Formula works by Substituting (in essence, Padding) each occurance of the '/' Forward Slash with a ' ' Space the Length of "B1" and then returning the last 'n' digits the Length of "B1" which will be the last occurance Padded out to a Length of 16. Finally =TRIM is used to remove the Whitespace leaving the '10000'. In Cell "b2" the data is '10/10/B001/10000' and we want to return the '10000' part, the last part after the last '/' Forward Slash:

' in Cell "B1" using =RIGHT, the data 10/10/B001/10000 is parsed by the Formula below giving us the last part after the '/' Forward Slash, '10000' =INT(TRIM(RIGHT(SUBSTITUTE(B1,"/",REPT(" ",LEN(B1))),LEN(B1)))) ' similarly, using =LEFT will give us the first occurance '10' =INT(TRIM(LEFT(SUBSTITUTE(B2,"/",REPT(" ",LEN(B2))),LEN(B2)))) ' to get the second occurance we can use the following Formula =TRIM(MID(B1,LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",1))+1)),LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",2))))-LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",1))+1)))) ' to get the third occurance we can use the following Formula =TRIM(MID(B1,LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",2))+1)),LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",3))))-LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",2))+1)))) ' this Formula builds up the Parsing, first getting '10', then '10/10' and then finally '10/10/B001' =LEFT( B1, FIND( "|", SUBSTITUTE( B1, "/", "|", 1) ) - 1 ) =LEFT( B1, FIND( "|", SUBSTITUTE( B1, "/", "|", 2) ) - 1 ) =LEFT( B1, FIND( "|", SUBSTITUTE( B1, "/", "|", 3) ) - 1 ) ' more Examples - get the first occurance before the '/' which is '10' =LEFT(B1, SEARCH("/",B1,1)-1) ' get the second occurance before the '/', again '10' =MID(B1, SEARCH("/",B1) + 1, SEARCH("/",B1,SEARCH("/",B1)+1) - SEARCH("/",B1) - 1)

If you want to find out the First or Last time a value appears across many Columns of data, for example the First time a Sale was made in 2016 or the Last time something Sold being in 2017, then you can use the Formula below or open the Workbooks to see how I do it. Here is the First Sold In Example.xlsx (996 KB) Workbook. Here is the Last Sold In Example.xlsx (773 KB) Workbook:

' first Sold In Formula =INDEX(D$1:CU$1,MATCH(TRUE,INDEX(D2:CU2<>"",),0)) ' Last Sold In Formula which also tells you if something 'Never Sold' at all within the Range =IFERROR(LOOKUP(9.999999999E+307,C2:FO2,$C$1:$FO$1),"Never Sold")

If you want to Transpose a Block of 5 Rows of data into Columns then you can use the following Formula - modify it to do less or more than 5 Columns by adjusting the number '5':

=INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))

Sometimes you may want to COUNT or SUM Multiple Columns so that if a Row is Filtered in an Autofilter or the Row is Hidden it will not be included in the Formula ie. I want to COUNT and SUM Sales for Products where the percentage share >95%, with data including Headers in Cells "B4:E8", then I could use the following Formulas (check out the File for more information). The Formula use SUMPRODUCT, SUBTOTAL, OFFSET & MIN to calculate the values. Here is the Using SUMPRODUCT with Hidden or Filtered Rows.xlsx (10.0 KB) Workbook:

' my data in Cells "B4:E8" and Cell "C14" = 95 (add a Filter to the data Range and the Formula below - filter the Range) Product Product Description Sales 10001 Funkin RaspMoj Car 6X1 72.1% £31,655.86 10002 Cragganmore 12 BOT 6x0,7 96.9% £26,564.20 10003 Smirnof &Cranb CAN 12X0,25 99.2% £20,990.45 10004 Funkin Lemon Car 5x1 Puree 10.8% £11,328.00 ' COUNT =SUMPRODUCT((D5:D8>=C14)*(SUBTOTAL(103,OFFSET(E5,ROW(E5:E8)-MIN(ROW(E5:E8)),0)))) ' SUM =SUMPRODUCT((D5:D8>=C14)*(SUBTOTAL(109,OFFSET(E5,ROW(E5:E8)-MIN(ROW(E5:E8)),0))))

Sometimes you may want to have a rolling ISO Week Number in your Workbook that is Formatted differently and that you can drag right across all of your Columns as Headers. I will give you 2 Formulas that you can use to do this. The first one is in 2 parts, meaning that from a Date in Cell "A1" you have to enter both Formulas - the second one can be dragged across your Worksheet. The second one is a single Formula that can just simply be dragged across your Worksheet. The Formula will produce rolling ISO Week numbers as your headers using the Format X.XXX ie. 7.2018, 8.2018, 9.2018, 10.2018 -> 52.2018, 1.2019 etc. You can easily change the formatting by prefixing with ="wk "& to add a 'wk' prefix ie. wk 1.2019, wk 2.2019 or even use Text() to format the Week numbers 1->53 as 01, 02, 03 etc. using Text(,"00") as the wrapper for the first part of the INT Formula. Here is the How to Setup and Format a Rolling ISO Week Number.xlsx (12.0 KB) Workbook:

' 2 parts - add the Date in Cell "A1", add Formula 1 to any Cell for the current ISO Week and then add Formula 2 to any Cell and drag across =INT(($A$1-DATE(YEAR($A$1-WEEKDAY($A$1-1)+4),1,3)+WEEKDAY(DATE(YEAR($A$1-WEEKDAY($A$1-1)+4),1,3))+5)/7)&"."&YEAR($A$1-WEEKDAY($A$1-1)+4) =INT(($A$1+INT((COLUMN(A:A))*7)-DATE(YEAR($A$1+INT((COLUMN(A:A))*7)-WEEKDAY($A$1+INT((COLUMN(A:A))*7)-1)+4),1,3)+WEEKDAY(DATE(YEAR($A$1+INT((COLUMN(A:A))*7)-WEEKDAY($A$1+INT((COLUMN(A:A))*7)-1)+4),1,3))+5)/7)&"."&YEAR($A$1+INT((COLUMN(A:A))*7)-WEEKDAY($A$1+INT((COLUMN(A:A))*7)-1)+4) ' single Formula - add the Date to Cell "A1" and drag across =INT(($A$1+INT((COLUMNS($A$1:A1)-1)*7)-DATE(YEAR($A$1+INT((COLUMNS($A$1:A1)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A1)-1)*7)-1)+4),1,3)+WEEKDAY(DATE(YEAR($A$1+INT((COLUMNS($A$1:A1)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A1)-1)*7)-1)+4),1,3))+5)/7)&"."&YEAR($A$1+INT((COLUMNS($A$1:A1)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A1)-1)*7)-1)+4) ' using a Text format to show leading zeros =TEXT(INT(($A$1+INT((COLUMNS($A$1:A4)-1)*7)-DATE(YEAR($A$1+INT((COLUMNS($A$1:A4)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A4)-1)*7)-1)+4),1,3)+WEEKDAY(DATE(YEAR($A$1+INT((COLUMNS($A$1:A4)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A4)-1)*7)-1)+4),1,3))+5)/7),"00")&"."&YEAR($A$1+INT((COLUMNS($A$1:A4)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A4)-1)*7)-1)+4) ' adding on a Week prefix ="wk "&TEXT(INT(($A$1+INT((COLUMNS($A$1:A7)-1)*7)-DATE(YEAR($A$1+INT((COLUMNS($A$1:A7)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A7)-1)*7)-1)+4),1,3)+WEEKDAY(DATE(YEAR($A$1+INT((COLUMNS($A$1:A7)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A7)-1)*7)-1)+4),1,3))+5)/7),"00")&"."&YEAR($A$1+INT((COLUMNS($A$1:A7)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A7)-1)*7)-1)+4)

Sometimes you may want to use the Columns() Function in a Formula but have the initial result coerse to Zero (0), however the Function will fail because there isn't a Column zero - this Formula will do that for you:

' standard method COLUMN(A:A) ' setting the first result to zero (0) =(COLUMNS($A$1:A1)-1)*7

If you want to highlight Dates in a Column that are in the current Year if a File is opened say in 2 Years time or if the Dates are in a specific Year ie. 2018 no matter when the File is opened at some future Date, then check out the Conditional Format Formula and the example File below. Here is the Date Format.xlsx (12.0 KB) Workbook:

' current Year any time the file is opened in the future =YEAR(F3)=YEAR(TODAY()) ' specific Year no matter whenever the file is opened in the future =YEAR(H3)=2018

If you want to SUM or Average the Top 10 or the Top 'X' numbers in a Range then you can use the Formula below (add or remove the comma-delimited list according to how many of the Top numbers you wish to use in the calculation) - see the download File for examples. Here is the AVG Top 10 Numbers.xlsx (11.0 KB) Workbook:

' Average =AVERAGE(LARGE(C1:V1,{1,2,3,4,5,6,7,8,9,10})) ' SUM =SUM(LARGE(C2:V2,{1,2,3,4,5,6,7,8,9,10}))

Here are some alternative Characters that you can use in Filepaths in VBA or Excel Formula and in Worksheet Tabs - normally these are not allowed ie. colon ':' or forward slash '/' - Copy & Paste these into your Code or Worksheet Tabs / Formula:

' // colon ':' ꞉ ' // forward slash ':' ∕

Here are formula to accomplish specific Date tasks, like finding the first Monday of a specific Year

' find the first Monday of a Year from a Date. enter this Formula into Cell "B1" for a Date in Cell "A1" =DATE(YEAR(A1),1,8)-WEEKDAY(DATE(YEAR(A1),1,6)) ' find the first Monday in December from a Date. enter this Formula into Cell "B1" for a Date in Cell "A1" =DATE(YEAR(A1),12,8)-WEEKDAY(DATE(YEAR(A1),12,6)) ' find the last Monday in December from a Date. enter this Formula into Cell "B1" for a Date in Cell "A1" =DATE(YEAR(A1),12, 31)-WEEKDAY(DATE(YEAR(A1),12, 29)) ' find the first Monday of a Year NOT in Date Format ie. 2019, 2020. enter this Formula into Cell "B1" for a Year in Cell "A1" =DATE(A1,1,1)+CHOOSE(WEEKDAY(DATE(A1,1,1),2),0,6,5,4,3,2,1)