Here are some examples of Excel Formula and useful Links. 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
To use the Formula examples, highlight all of the Formula within the box boundries using the Mouse and then press CTRL+C to copy to the Clipboard
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. Some regularly used Symbols are also included at the bottom:
' 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* ;"—"
' you can pad out the spaces if you want to nudge stuff right a bit although left align will always take precedent
#,##0* ;#,##0* ;"—"
' allows you to display a Wide line for zeros in readiness for Text entry
General;-General;"—"
' display plus (+) and minus (-) prefix as a Percentage (%)
+ #0.0# %;[Red]- #0.0# %
' hide zero's for number or the return value from a Formula that equals zero but leave Text unchanged
' Please Note: this does NOT work if you wrap a Function using =Proper()
0;;;@
0;-0;;@
' hide Numbers and Text in a Cell
;;;
' hide zero's in a Cell
#,##0;-#,##0;
' complex, slash out a Literal \/, use Text by wrapping in quotes "FOOBAR", use spaces for padding __, use Character after * (asterix) to fill up the Cell *!
\/"FOOBAR"__*!
' Format as £1K for an entry of 1000
£#,##0,K_);(£#,##0,K)
' use Symbols for Up/Down Arrows ▲/▼ (Arial Geometric Shapes (subset) variety)
▲0.0%;▼0.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 the Font in another Colour wihout Conditional Formatting, Colour the Font for Positive ie. Green, then add one of these as a Custom Colour Format
' [Black], [Green], [White], [Blue], [Magenta], [Yellow], [Cyan], [Red] with leading + or - symbols
+0.0%;[Red]-0.0%;0.0%
' for one of the other 56 Excel Colours, you can add this to the VBA Immediate Window and press Enter to see the Formats in Column A
for x = 1 to 56:cells(x,1) = "FOOBAR":cells(x,1).numberformat = "[COLOR"&x &"]@":Next x
' from here, it follows then, that you can use this for a Greenish-brown Colour
+0.0%;[Color12]-0.0%;0.0%
' and postive and negative Green, Red without +/- symbol
[Color50] #, ##0; [Color53]#, ##0
' Colour Red for anything less than 90
[Red][<90]0;0
' Colour Red for anything less than 50, Colour Green for anything greater than or equal to 100
[Red][<50] #,##0;[Color10][>=100] #,##0
' Colour Orange with Text prefix for less than 90
[Color46][<90]"Too low "0;0
' WK7, WK12 etc. where Cell entry only has the numeric value
"WK"##
' Time 09:00 AM (09:00), 03:00 PM (15:00)
hh:mm AM/PM
' Time 9:00 AM (09:00), 3:00 PM (15:00)
h:mm AM/PM
' Time 09:00 for Due @ 9 AM
"Due" \@ h:mm AM/PM
' Fractions 0.5 as 1/2 (half)
?/?
' Fractions 1.5 as 1 1/2 (one and a half)
# ?/?
' Percentages with negative wrapping in brackets
#.0%;(#.0%);
' Scientific =3.14*10^9 as 3.14E+09
0.00E+00
' Repeating Leading and Trailing Characters, type Enter in Cell, for Enter ____________________
@ *_
' Reverse Repeating Leading and Trailing Characters, type Enter iin Cell, for .................. Enter
*. @
' Sigma Code or SUM/Total Symbol
Σ
' Average, AVG Symbol
∓
' Custom Cell Format to use with 3 Icons, a Green Tick, Red Cross and an Orange Exclamation, in order of precedent, positive, negative and then zero
' ie. Cell = 1 or 2, then Green Tick, Cell = -1, then Red Cross and Cell = 0, then Orange Exclamation
[Color10] "✔";[Color3] "✖";[Color45] "!"
' Custom Cell Format to use with 3 Icons, a Green Circle, Red Circle and an Orange Circle, in order of precedent, positive, negative and then zero
' ie. Cell = 1 or 2, then Green Circle, Cell = -1, then Red Circle and Cell = 0, then Orange Circle
[Color10] "⬤";[Color3] "⬤";[Color45] "⬤"
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 first Formula (alternatives are given to grab different dynamic Ranges):
' will grab everything from Cell "A1" to the last Header Cell in the first Row and all the way down the Range based upon the contiguous Column data "A"
=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
' will do the same as the above, but will start in Column "C" based upon the contiguous Column data "C"
=OFFSET($C$1,0,0,COUNTA($C:$C),COUNTA($1:$1))
' will do the same as the above, but will grab Column "C" and "D" by specifying 2 Columns
=OFFSET($C$1,0,0,COUNTA($C:$C),2)
' will do the same as the above, but will grab from "C2"
' by shifting down 1 Row we need to minus 1 Row from the Columns ie. -1
=OFFSET($C$2,0,0,COUNTA($C:$C)-1,2)
' will look down a separate Column until the last Match of a specific Text and grab the exact same length of Range from another Column
' for example Match all Rows containing "Final Forecast" in Column "B" and grab a Product list from Column "D" starting at "D3" hence we minus 3 Rows
' this is a very useful and powerful little dynamic Formula
=OFFSET(HL!$D$3,0,0,MATCH("Final Forecast",HL!$B:$B,0)-3,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
Create All Combinations from 2 Lists and 3 Lists using Ranges or Defined Names:
Here are a couple of examples of creating and using a LAMBDA Function in Excel to create a list of all of the possible combinations based upon 2 Lists or 3 Lists - you can use Ranges or Defined Names or dynamic Named Ranges (you need to add the LAMBSA Function as a Formula to a Named Ranged as per below):
' add a Formula called AllCombinations2Lists as a Named Range
=LAMBDA(array1,array2, LET(txt,TOCOL(array1&"@"&TRANSPOSE(array2)), HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(txt,"@"))))
' in any Cell as Named Ranges or standard Ranges
=AllCombinations2Lists(Fruit,Pets)
=AllCombinations2Lists("A2:A4","B2:B3")
' Post-sort by Fruit or by Pet
=SORT(CreateCombinations("A2:A4","B2:B3"),1)
=SORT(CreateCombinations("A2:A4","B2:B3"),2)
' add a Formula called AllCombinations3Lists as a Named Range
=LAMBDA(array1,array2,array3, LET(txt,TOCOL(TOCOL(array1&"@"&TRANSPOSE(array2))&"#"&TRANSPOSE(array3)), HSTACK(TEXTBEFORE(txt,"@"),TEXTBEFORE(TEXTAFTER(txt,"@"),"#"),TEXTAFTER(txt,"#"))))
' in any Cell as Named Ranges or standard Ranges
=AllCombinations3Lists(MyFruit,MyPets,MyNames)
=AllCombinations3Lists("A1:A5","B1:B3","C1:C2")
Transpose Data Stored in a Cross Table to a Sorted List:
Here we will create 2 LAMBDA functions to transpose data stored in a Cross Table to a sorted list (optional Filter out zeros) as a Named Range that can instantly transpose the data:
' add a Formula called CrossTableToList as a Named Range to Transpose & Sort the data
=LAMBDA(array1,array2,array3,LET(txt,SORT(TOCOL(array1&"@"&array2)&"#"&TOCOL(array3)),HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(TEXTBEFORE(txt,"#"),"@"),TEXTAFTER(txt,"#"))))
' use the LAMBDA function, SKUS are a Named Range of Vertical Materials
' Depots are a non-sorted, Named Range, Column list of Plants and Data is a
' Named Range of the values
=CrossTableToList(SKUS,Depots,Data)
=CrossTableToList("A2:A10","B1:E1","B2:E10")
' to Filter out zero values ("0") or Blanks use FILTER() on HSTACK()
' create the LAMBDA test function
=LAMBDA(array1,array2,array3,LET(txt,SORT(TOCOL(array1&"@"&array2)&"#"&TOCOL(array3)),FILTER(HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(TEXTBEFORE(txt,"#"),"@"),TEXTAFTER(txt,"#")),TEXTAFTER(txt,"#")>"0")))(SKUS,Depots,Data)
' add a Named Range called 'CrossTableToListExcludeZeros' with the following
=LAMBDA(array1,array2,array3, LET(txt,SORT(TOCOL(array1&"@"&array2)&"#"&TOCOL(array3)), FILTER(HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(TEXTBEFORE(txt,"#"),"@"),TEXTAFTER(txt,"#")),TEXTAFTER(txt,"#")>"0")))
' use the LAMBDA function
=CrossTableToListExcludeZeros(SKUS,Depots,Data)
=CrossTableToListExcludeZeros("A2:A10","B1:E1","B2:E10")
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)
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 find the first Positive Value in a Range or Number of Columns adapt the array Formula below (works without braces in new versions of Excel):
' data is in columns from A1 to H1, 0,0,blank,blank,-1,blank,3,5 and will find 3
=IFERROR(INDEX(A1:H1,MATCH(TRUE,A1:H1>0,0)),0)
To find the Last Row in a Column or Number of Columns or Range:
' Column D
=MAX(ROW(D:D)*(D:D<>""))
' Columns A-Z
MAX(ROW(A:Z)*(A:Z<>""))
Use All() for more than 1 type of value or item, otherwise just identify the item. This is useful in a Chart Cell for example to pull in a single value at an Aggregate level for a Filtered Range or Table or All() meaning there are more than 1 type of value to be displayed and the Aggregate level cannot display all of them. This way you can build a String of Filtered items:
' for a Filtered list in Column "B"
=IF(SUMPRODUCT(1/COUNTIF($B$1:$B$3000,$B$1:$B$3000&""))>2,"All()",$B$1&"")
' use this array Formula with a Header in Cell "A1" and data in Cell "A2:A100". Enter using CTRL+Shift+Enter
=INDEX(A2:A100,MATCH(1,SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),0))
If you have forgotton your Excel Sheet Password and are locked out, then this is easily rectified by following the steps below to unlock any of the required Worksheets within the Workbook:
Backup the file
Rename the extension of your spreadsheet from .xlsx, .xlsm etc. to .zip
Open the "zip" file and Locate the "xl" folder and then the "worksheets" folder
Inside the "worksheets" folder you will see a list of all your worksheets within your spreadsheet
They will be listed as sheet1.xml, sheet2.xml etc. I would copy these out to your Desktop
open each file in Notepad and search for the line that begins with:
<sheetProtection algorithmName=”SHA-512″ hashValue=
Here is an example:
<sheetProtection algorithmName=”SHA-512″ hashValue=”x9RyFM+j9H4J3IFFhsHo3q1kQkuLydpJlLh2mdvfvk15He/Yps8xizWt/XkAJ//g+TyqgcU+8o1QBjQvKDqIzg==” saltValue=”57YXDPnVjawU5s1nGyT8fQ==” spinCount=”100000″ sheet=”1″ objects=”1″ scenarios=”1″/>
Just remove the entire line and save the file. Copy all of the modified files back into the "worksheets" folder overwriting the existing files
Rename the file back to whatever the extension was before changing it to "zip"
Reopen the file
If you want to Search and Find or Find and Replace Content in a Worksheet you can use the '?' symbols as shown below in the Find & Replace Dialog by pressing CTRL+F:
' if you want to Find any 2 characters but not single characters in a Worksheet use
??
' or just use ? to find anything
?
If you want to Sort numbers differently instead of Smallest to largest, you need to change the numbers to Text by appending double quotes and then Copy and Paste these back as Special Values. Then Sort again
=B2&""
If you want to Sum Multiple Columns by a Criteria then you can use this Formula:
' Criteria is in Sheet1, Cell A1. Matching Criteria Column is in Sheet 2, Column A. Columns to SUM are in Sheet2, H:S. A fixed Range is maintained
=SUMPRODUCT((Sheet2!$A$36:$A$1500=Sheet1$A1)*(Sheet2!$H$36:$S$1500))
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 within the Function
=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)
Here is a Formula to extract just the Cell Letters:
' add to Cell "A1"
=MID(SUBSTITUTE(CELL("address",A1),"$",""),1,LEN(SUBSTITUTE(CELL("address",A1),"$",""))-1)
Here is a fix. Open the Clipboard via the Ribbon on the Home Tab. Click the little options box and then uncheck everything - now retry the Copy & Paste:
If you are having issues using SUM, before you use these options, check that the underlying Format of the Cell you are using is the correct Number Format and not a Date. To SUM Numbers that may be stored as Text (assuming your Range to SUM is 'A1:C1') you can append &"" to the SUM or 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):
' first of all try this
=SUM(A1:C1)&""
' 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))}
' alternative method for a Filtered Table Column for a fixed Range - adjust accordingly. remember to enter this as an Array Formula by pressing CTRL+SHIFT+ENTER
' the Formula below this one will also identify the very first item in the Filtered list. Formula one gives total unique filtered and Formula 2 gives the first item
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C22,ROW($C$22:$C$3000)-ROW(C22),0,1)),MATCH($C$22:$C$3000,$C$22:$C$3000,0)),ROW($C$22:$C$3000)-ROW(C22)+1)>0,1))}
{=INDEX(C1:C3000,MIN(IF(SUBTOTAL(3,OFFSET(C22:C3000,ROW(C22:C3000)-MIN(ROW(C22:C3000)),,1))<>0,SUBTOTAL(3,OFFSET(C22:C3000,ROW(C22:C3000)-MIN(ROW(C22:C3000)),,1))*ROW(C22:C3000))))}
' alternative method with a fixed Range. remember to enter this as an Array Formula by pressing CTRL+SHIFT+ENTER
{=SUMPRODUCT(1/COUNTIF($B$22:$B$3000,$B$22:$B$3000))}
' in a Table Column for unique, distinct values when filtered - change the Table Name and the Column Header. remember to enter this as an Array Formula by pressing CTRL+SHIFT+ENTER
{=SUM(--(FREQUENCY(IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)),COUNTIF(Table2[First Name],"<"&Table2[First Name]),""),COUNTIF(Table2[First Name],"<"&Table2[First Name]))>0))}
The best way and a fast way using a Formula is to use COUNMTA with UNIQUE as shown below (Please note: you have to use '-1' at the end if the Formula returns an extra item if you have Blanks in any Cells):
' count the unique items in the Range "C5:C9"
=COUNTA(UNIQUE(C5:C9))
I use this method whenever I want to perform an operation ie. SUM a Range where the Week Number at the top of the Column matches a Week Number in a Cell of allowed Week Numbers:
' only allow operation to happen if the Week Number in Cell "N5" is found in this Pipe-delimited String in Cell "H5"
=IF(ISNUMBER(SEARCH("|"&H$5&"|",$N$5)),1,0)
Here is a Formula that calculates 15 weeks prior to the baby's expected Date of birth and then uses the start Date of the Employee to ensure that they have been in full employment for at least 26 weeks prior to this Date
' I2 := the expected date of birth. H2 := the Employee start date
=IF(INT(($I2-7*15-$H2)/7)>=26,"Y","N")
' error checked on both date Cells, date format DD/MM/YYYY
=IF(AND(NOT(ISERROR(DATEVALUE(TEXT($H2,"dd/mm/yyyy")))),NOT(ISERROR(DATEVALUE(TEXT($I2,"dd/mm/yyyy"))))),IF(INT(($I2-7*15-$H2)/7)>=26,"Y","N"),"")
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 a useful example of how to find the nearest number in a list of numbers by entering a number to examine. List start in "B3". Formula add to "E3". Value to examine enter in "E2" (taken from https://www.extendoffice.com/documents/excel/1050-excel-find-closest-value.html):
{=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))}
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:
Sub UnlockedR1C1()
Range("C3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],RC[2]:R[2]C[3],2,0)"
End Sub
Sub LockedR1C1()
Range("C3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R2C5:R4C[3],2,0)"
End Sub
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)),"")
' this one works with Table Columns if you have an issue with the above - taken from https://trumpexcel.com/unique-items-from-a-list-in-excel/
' simply swap all occurences of '$A$2:$A$11' with your Table Column (hover above Table until you get the little arrow to select)
=IFERROR(INDEX($A$2:$A$11,SMALL(MATCH($A$2:$A$11,$A$2:$A$11,0), SUM((COUNTIF($A$2:$A$11,$C$1:C1)))+1)),"")
Here are some examples of extracting unique items from lists in Excel. One of the Workbooks allows you to bring back multiple Columns of data for an Item in a List
Here is a Unique List and In List Only Once.xlsx Workbook
Here is a Unique List Formula.xlsx Workbook
Here is a Bring back all Data in Other Columns for a Matching Item on a List.xlsx Workbook
' add some Items to "A2" onwards and add this Array Formula to another Cell - drag down to reveal the unique Items but in a non-sorted order
' - sometimes you may NOT want a list sorted!
{=INDEX($A$2:$A$6, MATCH(0, COUNTIF(A$1:$A1, $A$2:$A$6), 0),1)}
' add some Items to "A2" onwards and add this Array Formula to another Cell - drag down to reveal the unique Items but in a sorted order
' Blank Rows will stop the process
{=INDEX(List,MATCH(0,COUNTIF(C1:$C$1,List)+(COUNTIF(List,List)<>1),0))}
' unique list starting in Cell "B2" with list in Cell "A2" onwards, add as an array Formula
' - errors are Blank as are non-matches
{=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0))&"","")}
' only in a list once starting in Cell "C2" with list in Cell "A2" onwards
{=IFERROR(INDEX(List,MATCH(0,COUNTIF(C1:$C$1,List)+(COUNTIF(List,List)<>1),0)),"")}
' with list data in Cells "A1:C7" add this Formula to Cells "B10" and "C10" as Array Formula and drag down. Then type in an Item in the List in Cell "A10"
{=IFERROR(INDEX($A$1:$B$7,(SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))),2),"")}
{=IFERROR(INDEX($A$1:$C$7,(SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))),3),"")}
Here are some examples of using Data Validation to create Drop-down Combo Boxes built from dynamic Ranges of Items:
' add your list to "C2" and then add the Named Range to a Sheet called Sheet1 - call it ListA
=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$1000,SUMPRODUCT(--(Sheet1!$C$2:$C$1000<>"")))
' now add Data Validation to a Cell using List, and add =ListA to retrieve the values
' add your list to "C2" and then add the Named Range to a Sheet called Sheet1 - call it ListB
=OFFSET(Sheet1!$C$2,0,0,SUMPRODUCT(--(Sheet1!$C$2:$C$1000<>"")),1)
' now add Data Validation to a Cell using List, and add =ListB to retrieve values
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 {1,2,3,4,5} to use the Top 5 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:
You can also use a Letter to Identify a Switch, so you could have a Drop-down Combo Box with Q, M & W and depending on the decision/selected Letter then you can do your switch without lots of IF's
' 0,1,2 Boolean - 3-way (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")
' 0,1,2 Boolean with Letters (Q, M & W) - 3-way (2 tests)
=(A14<>"W")*1+(A14<>"M")*2
' Using the above to switch on one of 3 Letters. this means that you can use a different Formula for "Month", "Quarter" & "Week"
=CHOOSE(((A14<>"W")*1+(A14<>"M")*2),"Month","Week","Quarter")
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)
' for numeric values in a Row left to right, Ascending
' - Column can be replaced by 1,2,3 etc.
=SMALL($B$6:$H$6,COLUMN(A6))
' as above but Descending
=LARGE($B$6:$H$6,COLUMN(A6))
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 are the Formula to get the earliest and oldest (first and last) Dates in a Range by some Criteria in another Column. My Criteria is in Column "M6:M21", with the Dates in Column "N6:N21". Enter the Formula using CTRL+SHIFT+ENTER:
' get first Date for Criteria in Cell "M6"
{=MIN(IF($M$6:$M$21=M6,$N$6:$N$21))}
' get last Date for Criteria in Cell "M6"
{=MAX(IF($M$6:$M$21=M6,$N$6:$N$21))}
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 are ways 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:
' the Formula - 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)
' to make a pattern that repeats the Cell value down ROWS, use INDEX like this with Values 1,2,3,4,5 in Column C - drag formula down the Rows
=INDEX(C:C,INT((ROW(1:1)-1)/5)+1)
' to make a pattern that repeats the Cell value across COLUMNS, use INDEX like this with Values 1,2,3,4,5 in Column A - drag formula across the Columns
=INDEX($A:$A,INT((COLUMN(A:A)-1)/5)+1)
' for Column values 1,2,3,4,5 in A1 to E1 use this formula to drag across COLUMNS to give the same Cell value as a reference or input into a function 5 times and then refer to the next Cell value for another 5 times - this is pretty cool ;)
=INDEX(1:1,INT((COLUMN(A:A)-1)/5)+1)
' so for 2 Rows of data stored in F10:O10 (1-10) and F11:O11 (various random values) and the formula in R11, drag across Columns to refer to the Cell value 5 times (first 5 Columns) and then the next Cell value on the 6th time (the 6th Column) - this is even cooler ;) ... note, remmebr to lock the Columns in the first part of the formula
=INDEX($F11:$O11,INT((COLUMN(A:A)-1)/5)+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 great way to Copy and Paste repeated values in a Column (or Columns) when you only have the Item in the first Column and then many Items in the next Column and you want the first Item repeated in-line with the second Column:
Select Range
Home, GO to Find & Select
Go to Special
Blanks
Enter first Cell in Formula Bar
Press CTRL+ENTER
This will copy down the repeated data for blanks in multiple column
1. Select the Column Range
2. Ribbon->Home Tab, GO to Find & Select on the RHS
3. Select 'Go to Special'
4. Choose 'Blanks' and click 'OK'
5. Enter the first Cell reference in Formula Bar for the initial Item ie. "A1" if the initial Item is in Cell "A1"
6. Press CTRL+ENTER at the same time
This will copy down the repeated data for blanks in multiple column(s), cool eh?
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. Here is the How to Setup and Format a Rolling ISO Week Number.xlsx Workbook:
' 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)
' create a Formatted Rolling ISO Week number - enter any Cell and then drag Formula Right anywhere on a Sheet
="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)
' create a Formatted Rolling ISO Week number from 2 Named Ranges with a Date in Cell "A1" - enter any Cell and then drag Formula Right anywhere on a Sheet
' ISOYear
=YEAR($A$1+INT((COLUMNS($A$1:A7)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A7)-1)*7)-1)+4)
' ISOWeek
Sometimes you may need to use a Column Formula that needs to start with zero (0) as opposed to 1 - here are a couple of ways to do it:
' increments of 1
=COLUMNS($A$1:A1)-1
' increments of 7
=(COLUMNS($A$1:A1)-1)*7
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 and also Last & First Sold Example.xlsx Workbook. To exclude Zeros download the Last & First Sold Example Excluding Zeros.xlsx Workbook:
' first Sold In Formulas, the second one also tells you if something 'Never Sold' at all within the Range
=INDEX(D$1:CU$1,MATCH(TRUE,INDEX(D2:CU2<>"",),0))
= IFERROR(INDEX(D$1:CU$1,MATCH(TRUE,INDEX(D2:CU2<>"",),0)) ,"Never Sold")
' 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")
' to exclude zeros, add in an IF Statement
=IFERROR(LOOKUP(9.999999999E+307,IF(C2:CG2<>0,C2:CG2),$C$1:$CG$1),"No Sales Yet")
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 exclude zeros, add in an IF Statement
=IFERROR(LOOKUP(9.999999999E+307,IF(C2:CG2<>0,C2:CG2),$C$1:$CG$1),"No Sales Yet")
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'. Enter a list of data in Column "A", add the Formula to Cell "B1" and drag accross the Column Range:
=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
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 is the Adding and Subtracting Date Times.xlsx Workbook
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)
Place the Formula in Cell "C1" with your data in Column "A" and drag across 3 Columns. Change the 3's to any Number you want:
=INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))
Here are some Array Formulas to reorder lists in Excel, pushing any zero, Blank Cells or Cells containing Formula that return Blank "", to the bottom of the list. The Formula is an array Formula added using CRTL+Shitf+Enter. Original list should be entered in Cells "A1:A8" and your Formula should be entered in Cell "B1" and dragged down to Cell "B8". An example using a Non-array Formula is also given below and finally I have added a Google Sheets solution. I have also added an example Worksheet to download. Please Note: these Formula DO NOT sort anything - that is NOT the idea!:
' In Excel will reorder pushing zeros to the bottom of a list
{=IFERROR(INDEX($A$1:$A$8,AGGREGATE(15,6,(ROW($A$1:$A$8)-ROW($A$1)+1)/($A$1:$A$8<>0),ROWS(B$1:B1))),0)}
' In Excel will reorder pushing blank Rows to the bottom of a list
{=IFERROR(INDEX($A$1:$A$8,AGGREGATE(15,6,(ROW($A$1:$A$8)-ROW($A$1)+1)/($A$1:$A$8<>""),ROWS(B$1:B1))),"")}
' In Excel for Blanks only (List starts in Cell "A2" with normal Formula in Cell "B2")
=IFERROR(INDEX($A:$A,SMALL(INDEX(ISBLANK($A$2:$A$10)*10^10+ROW($A$2:$A$10),0),ROW(A1))),0)
' In Excel will reorder Text and Blanks pushing zeros to the bottom of a list (List starts in Cell "A2" with array Formula in Cell "B2")
' GOOGLE SHEETS Solution
{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISTEXT($A$2:$A$10),ROW($A$1:$A$9),""),ROW(A1))),0)}
' lastly this Formula will use the LEN() Function to filter out Cells that contain a Formula but that maybe Blank as ISBLANK() will not work correctly for these
{=IFERROR(@INDEX($A:$A,SMALL(INDEX((LEN($A$2:$A$10)=0)*10^10+ROW($A$2:$A$10),0),ROW(A1))),"")}
Here is the Testing Reordering.xlsx Workbook that can be used in Excel (and Google Sheets for the Formula in Column "C" on the Tab 3 Sheet. Other Tabs give you the solution for Google Sheets and for reordering when using Formula that may return Blank ie. "" - check out Sheets 4 & 5 ;)
Place the first Formula in Cell "B1" with the Value '15.125' in Cell "A1" to identify the Whole, Integer part. Place the second Formula in Cell "C1" to identify the Remainder, Decimal/Fractional part:
' Integer part before Radix Point
=TRUNC(A1,0)
' Remainder Part after Radix Point
=A1-TRUNC(A1,0)
You can use the ROWS() or COLUMNS() Functions to achieve this by locking one part of the Formula:
' Rows - SalesStartDate is a Cell with a Date in it, ie. 01/01/2018, so this Formula increase the Month each time you drag down, Format is mmm yy
=SalesStartDate (first Cell)
=EDATE(SalesStartDate,ROW(165:165)-ROW($164:$164))
' Columns - SalesStartDate is a Cell with a Date in it, ie. 01/01/2018, so this Formula increase the Month each time you drag right, Format is mmm yy
=SalesStartDate (first Cell)
=EDATE(SalesStartDate,COLUMN(N:N)-COLUMN($M:$M))
You can use the following Formula to find the last non-blank Cell in a Row Range:
' in a Row
=ADDRESS(ROW(),LOOKUP(2,1/(Sales!B11:CV11<>""),COLUMN(Sales!B11:CV11))-2)
=ADDRESS(ROW(),SUMPRODUCT(MAX((B11:CV11<>"")*COLUMN(B11:CV11))))
' in a Range
=ADDRESS(ROW(),SUMPRODUCT(MAX((B11:CV373<>"")*COLUMN(B11:CV373))))
You can use the following Formula to Switch Columns into Rows Using a Formula and visa versa (you can also use the return result as normal in a formula calculation ie. Dates for example):
' from a Column to a Row
=INDEX($A$4:$J$4,ROW(A1))
' from a Row to a Column
=INDEX($A$4:$A$10,COLUMN(A1))
You can use the following Formula to Count the Number of Occurrences of a Single Character in One Cell:
' count the number of occurances of the pipe character (|) in a Cell
=LEN(A2)-LEN(SUBSTITUTE(A2,"|",""))
You can use the following Formula to Find the nth Occurrences of a Single Character in One Cell:
' find the 3rd occurance of the pipe character (|) in a Cell
=FIND("|",A2,FIND("|",A2)+2)
' or use
=FIND(CHAR(1),SUBSTITUTE(A2,"|",CHAR(1),3))