Excel Formulas
Formulas
To use the Excel features below, simply copy the formula and paste it directly into your Excel spreadsheet. Click here to download the workbook.
| Excel Name | Description | Formula |
|---|---|---|
| Excel 1 | Converting from a Number to Text | =TEXT(A3,"$[>=1000000]0.0,,\M; [>=1000] $0.0,K; $0.00") |
| Excel 2 | Converting from Text to a Number | =IFNA(LEFT(A3,LEN(A3)-1)*CHOOSE(MATCH(RIGHT(A3,1), {"K","M","B"},0),1000,1000000,1000000000),A3) |
| Excel 3 | Fix the number of decimal places imported from Excel into an Illustration | =FIXED(A2,2) |
| Excel 4 | Count Number of Cells in excel that are not hidden | =SUBTOTAL(103,a1:a10) |
| Excel 5 | Convert a Number and Text into a Number eg 320Sqm to 320 | =IF(ISNUMBER(SEARCH("Sqm",A1)),LEFT(A1,LEN(A1)-3)*IF(RIGHT(A1)="Sqm",1000,1),A1) |
| Excel 6 | Create Enterprise Lens Filters from Column Based Data | =UNIQUE(A1:A14) |
| Excel 7 | Remove the first word from a cell in Excel | =RIGHT(A1,LEN(A1)-FIND(" ",A1)) |
| Excel 8 | Date Manipulation | =TEXT((DATEDIF(A1,A2,"M")+1),"#")There are other Date Manipulation formulas in here too |
| Excel 9 | Concatenate values in a list and separate them by a "," | =TEXTJOIN(",",TRUE,A1:A24)Useful for creating EL Associations or comma separated lists |
| Excel 10 | Join the values of two cells together | =A1&B1 |
| Excel 11 | Rolled up Count (CountIF) | =COUNTIF(Range,A1) |
| Excel 12 | Rolled up Sum/Aggregate (SumIF) | =SUMIF(Range,A1,RangeToSum) |
| Excel 13 | Combining SumIF & Text format | =TEXT(SUMIF(Range,A1,RangeToSum),"$[>=1000000]0.0,,\M; [>=1000] $0,K; $0.00")Useful when you want to show $320M and not $320000000 |
| Excel 14 | Substituting values | ="S"&SUBSTITUTE(A1,",",",S") ="S"&SUBSTITUTE(A1," ","S") |
| Excel 15 | Replacing/Finding Values | =TEXTJOIN(",",TRUE,IFERROR("Risk"&FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,", ","</s><s>"),".","</s><s>")&"</s></t>","//s[.=number(.)]"),"")) |
| Excel 16 | Project Dates | =IF(J4>K4,"",(IF(AND(1 >= H4, 1 <= M4)=TRUE,"Jan"&J4,""))) |
Excel 1. Converting from a number to text
=TEXT(A1,"$[>=1000000]0.0,,\M; [>=1000] $0.0,K; $0.00")
Excel 2. Converting from Text to a number
=IFNA(LEFT(A1,LEN(A1)-1)*CHOOSE(MATCH(RIGHT(A1,1), {"K","M","B"},0),1000,1000000,1000000000),A1)
(This caters for Thousands, Millions and Billions)
Excel 3. Fix the number of decimal places imported from Excel into an Illustration
=FIXED(A1,2)
(Where A1 is the cell with the numberic value and 2 is the number of decimal places to import)
Excel 4. Count Number of Cells in excel that are not hidden
=SUBTOTAL(103,a1:a10)
(Where 103 is COUNTA but only for HIDDEN ROWS)
Excel 5. Convert a Number and Text into a Number eg 320Sqm to 320
=IF(ISNUMBER(SEARCH("Sqm",A1)),LEFT(A1,LEN(A1)-3)*IF(RIGHT(A1)="Sqm",1000,1),A1)
Excel 6. Create Enterprise Lens Filters from Column Based Data
=UNIQUE(A1:A14)
This will return all the unique values within this range. This is great for Enterprise Lens Filters or Lookup Lists
Excel 7. Remove the first word from a cell in Excel
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
Excel 8. Date Manipulation
=TEXT((DATEDIF(A1,A2,"M")+1),"#")
Where:
A2 = From Date
B2 = To Date
Excel 9. Concatenate values in a list and separate them by a ","
=TEXTJOIN(",",TRUE,A1:A24)
This could create a value such as: Jan2023, Feb2023, Mar2023, which could be used to create association values for an EPMO
Excel 10. Join the values of two cells together
=A1&B1
This will join A and B into a new value where they are concatenated together
Excel 11. Rolled up Count (CountIF)
=COUNTIF(Range,A1)
Where Range is the list of cells you want to check to see if any have a value that is equal to the value in cell A1.
Typically the range is column data on another worksheet within your excel file
Excel 12. Rolled up Sum/Aggregate (SumIF)
=SUMIF(Range,A1,RangeToSum)
Where
- Range is the list of cells you want to check to see if any have a value that is equal to the value in cell A1
- RangeToSum is the list of cells to Sum - should the condition above be true
- The Range and RangeToSum are typically columns of data on another worksheet within your excel file
Excel 13. Combining SumIF & Text format
=TEXT(SUMIF(Range,A1,RangeToSum),"$[>=1000000]0.0,,\M; [>=1000] $0,K; $0.00")
Excel 14. Substituting values
="S"&SUBSTITUTE(A1,",",",S")
="S"&SUBSTITUTE(A1," ","S")
The top formula expects no space after the comma eg 1,2,3,4
The bottom formula expects a space after the comma eg 1, 2, 3, 4
The could be useful when substituting values as numbers are more common and this would make the join id unique
Excel 15. Replacing/Finding Values
=TEXTJOIN(",",TRUE,IFERROR("Risk"&FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,", ","</s><s>"),".","</s><s>")&"</s></t>","//s[.=number(.)]"),""))
This formula might be useful in renaming complex JOINS within Enterprise Lens - making it easier to use with Attribute and Name Associations.
I used ChatGPT to create this formula in about 20 seconds and works for all versions from Excel 2013 onwards
Excel 16. Project Dates
=IF(J4>K4,"",(IF(AND(1 >= H4, 1 <= M4)=TRUE,"Jan"&J4,"")))
This is for a January date only. There is a separate formula for every Month
This fomula has been configured to work for any date range within a 6 year period. Every row (new Box) can be a separate 6 year timeframe.
There are a range of different Excel Formulas being used here. The From and To Dates must be side by side and the Yellow Box values must start in the VERY NEXT coloumn
Copy and Paste cells C4:CG4 to the first Cell beside the To Date.
The JOIN VALUES column is the value we will use for the ASSOCIATION from PROJECTS -> TIME
