29 Feb 2020

Sorting Sectors or Countries by Weights using Formulas

A portfolio of stocks is often grouped by industry sectors or countries. You can see how much of the portfolio is allocated to each sector or country in the table on the left below, which is sorted alphabetically. However, how do you sort this table on the left according to the portfolio weight using formulas?


First, you would sort the weights in ascending order. In the weight column, you would use the formula below:

Conceptual Formula:
=LARGE(RangeContainingWeights, RunningNumber)
where RangeContainingWeights is the range that contains the weights
RunningNumber is the numbers 1, 2, 3, 4, …

Example:
=LARGE($E$5:$E$15,1) gives you the largest number in the range
=LARGE($E$5:$E$15,2) gives you the 2nd largest number in the range, and so on.


Next, you would find the sector that matches the corresponding weight, using Index / Match.

Conceptual Formula:
=INDEX(RangeContainingSectors, MATCH(SortedWeight, RangeContainingWeights, 0))
where RangeContainingSectors is the range that contains the sectors
SortedWeight is the individual weights

Example:
=INDEX($D$5:$D$15,MATCH($I5,$E$5:$E$15,0))

Drag down the formulas and you will get the desired result:

 
Why use Formulas?
Of course, you can do a Data -> Sort from the menu bar on a one-off basis, but using formulas has the advantage of working automatically, without you needing to do the sorting every time the data changes.
 

Caveats
The sharper ones of you would have noted that the formula above does not work in some cases. The first case is if any two or more of the portfolio weights have identical values. Some additional handling (e.g. a helper column which sorts by alphabetical order) is required, if that is the case.

Another case is if any of the range that contains the weights has error or non-numerical values. Some scrubbing of the data beforehand or some error handling in the formula would be needed in this case.


Happy Sorting!

31 Jan 2020

How to Get Name of Worksheet, File, Folder and Path with Excel formula

There are times when we need to get the name of the Excel file, the path or the worksheet that we are working on. The following formulas show you how to do so.

Firstly, to get the full path, file name and current worksheet name in a cell, the formula is
=CELL(“Filename”)

The rest of the formulas needed are shown below. For each formula, you can replace "$C$4" with the formula in B$2 accordingly if you need the formula in a single cell.

However, in the unlikely event that any of your folders in the path or the file name contains square brackets "[" or "]", you would need a set of different formulas. (It's a pain, so try not to have any of your folders or file names to have square brackets!)

This latter set of formulas makes use of the formula for finding the last occurrence of a character in a string.

 

In summary, the formulas to find the following (assuming no square brackets in your folder path or file name) are:

(1) Folder path
=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)

(2) File name with path
=SUBSTITUTE(LEFT(CELL("Filename"),FIND("]",CELL("Filename"))-1),"[","")

(3) File name without path
=MID(CELL("Filename"),FIND("[",CELL("Filename"))+1,FIND("]",CELL("Filename"))-FIND("[",CELL("Filename"))-1)

(4) Sheet name
=RIGHT(CELL("Filename"),LEN(CELL("Filename"))-FIND("]",CELL("Filename")))

31 Dec 2019

How To Find Last Occurrence of Character in a String

There may be some instances where you will need to find the last occurrence of a specific character in a string in Excel. The following example shows you how.

Assuming that your original string is in cell B2 and the character that you want to find is "." , the formula to give you the position of the last occurrence of the character in the string is:
=LEN(B2)-LEN(TRIM(RIGHT(SUBSTITUTE(B2,".",REPT(" ",LEN(B2))),LEN(B2))))

The following shows a breakdown of the above formula:

The key to this example is this magical formula, which gives you everything to the right of the last occurrence of the character ".".
=TRIM(RIGHT(SUBSTITUTE(B2,".",REPT(" ",LEN(B2))),LEN(B2)))

This formula is useful if you want to find the extension of a filename (for example .xlsx or .docx) or the filename, folder name or worksheet name.

30 Nov 2019

Useful number format for stock prices in Asia

Imagine you need a number format to display stock prices from different countries so that they are easily readable. Some countries such as Singapore have stock prices to 2 decimal places, e.g. Singapore Airlines (S$10.77), or 3 decimal places, e.g. Starhill Global REIT (S$0.695), while others such as Korea have stock prices in the thousands with no decimal places, e.g. Samsung Electronics (KRW 2,581,000). The table below shows what you want:

A useful custom number format that you can use is: 

[>1000]#,###;[>=2]#,##0.000;0.00

Essentially, this number format says that:

– if the number is at least 1000, it will be displayed with no decimal places, with a comma as a thousands separator; 

– if the number is between 2 to 1000 inclusive, it will be displayed with 3 decimal places;

– the rest of the numbers (i.e. those less than 2) will be displayed with 2 decimal places.

This should work for the majority of share prices, though there would be some exceptions that will not be displayed to the desirable state, especially with US share prices, e.g. Amazon (US$ 1,508.95 will be displayed as US$1,509.)

For an overview of how custom number formats work, click here.

31 Oct 2019

How to find the first numerical value in a range

The following Excel formula will give you the first numerical value in a range (whether a row or column):

Conceptual Formula:

{=INDEX(RangeToSearch, MATCH(TRUE, ISNUMBER(RangeToSearch), 0)}

where RangeToSearch is a range such as $F$13:$P$13.

Example:

{=INDEX($F$13:$P$13, MATCH(TRUE, ISNUMBER($F$13:$P$13), 0))}

This formula works whether the range is a row or column. (but not for multiple rows/columns). It works even if there are #N/A or text values in the range.

However, the formula will give an #N/A if there is no number in the range. We can add error handling to the formula, with the following:

With error handling:

{=IF(COUNT(RangeToSearch)>0, INDEX(RangeToSearch, MATCH(TRUE, ISNUMBER(RangeToSearch),0)), ValueifError)

where ValueifError is the value if there are no numbers in the range. Count(RangeToSearch) counts the number of numerical values in the range RangeToSearch.

Example:

=IF(COUNT($F$23:$P$23)>0, INDEX($F$23:$P$23, MATCH(TRUE, ISNUMBER($F$23:$P$23), 0)), "No number in range")

Note: These are array formulas, so it must be entered using Shift-Ctrl-Enter.

30 Sep 2019

Overview of Custom Number Formats

A number format can have up to 4 sections, separated by semi-colons. In general, what each section applies to depends on the number of sections in total.

1 section: All numerical values

2 sections: Non-negative numbers; negative numbers

3 sections: Positive numbers; negative numbers; zero values

4 sections: Positive numbers; negative numbers; zero values; text

Conditional Formats
However, if the custom number format has conditional formatting, the above does not apply. A number format can have up to 2 conditions, with the last section reserved for text formatting and the 2nd last section reserved for other numbers that do not meet the conditions.

Conditions are included in square brackets and the following formatting will be applied if the condition is true. If there are 2 conditions in the number format, the 2nd condition will only be considered if the 1st condition is not true, so the ordering of the conditions is important.

Number formats can be used in cells, charts (e.g. for any axis or data labels) or in formulas using the TEXT formula.

Here are some examples of how conditional or custom formats can be useful:

Useful number format for stock prices in Asia

Shorten Big Numbers with K or M

If you want to learn more about number formats, here are some useful resources on Custom Number Formats & Conditional Number Formatting:

(1) This site describes the basics of number formatting – how sections are split if you have 1, 2, 3 or 4 sections, custom formats for numbers, text, date, time & miscellaneous.
https://www.sumproduct.com/thought/number-formatting

(2) This site covers some useful applications of custom number formats (thousands separators, colours, decimal places, dates, times, etc.)
https://peltiertech.com/Excel/NumberFormats.html

31 Aug 2019

Shorten Big Numbers with K or M

When we have large numbers, we often want to use the contraction "K" or "M" for thousands or millions respectively. To do this in Excel, we need to change the number format to the following.

For thousands (and 2 decimal places): [where 1,234 is shown as 1.23K]

0.00,"K"

For millions (and 2 decimal places): [where 1,234,567 is shown as 1.23M]

0.00,,"M"

One good use of number formats is in charts, as seen below. This uses the following format in the data labels:

0.00,,"M"

If you want to combine the thousands and millions format, you can use the conditional number format below. Numbers greater than 1 million are shown with an "M", and numbers greater than 1 thousand are shown with a "K".

[>=1000000]0,,"M";[>=1000]0,"K";0

For an overview of how custom number formats work, click here.