Category: Formulas

Percentiles

Percentiles are statistics which can help define where particular values sit in relation to the rest of the data set.   The formulas below show some examples based on the 20 values in column A: Cell C2 returns the value for P10 (the tenth percentile). Cell C3 returns the 25th percentile, which is also the Read More …

Net Promoter Score

Net Promoter Score (NPS) is a popular method of measuring customer satisfaction when analysing feedback provided on a 1 to 10 scale.   An NPS result returns anything between 100 and -100 so it can be a useful mechanism for converting customer feedback scored on a 1-10 basis into a percentage, whilst at the same Read More …

Data cleansing functions

When you import data from an external source, you will often find it isn’t in a desirable format for you to analyse. You will need to clean your data up first and thankfully there are lots of in-built Excel functions which can help.     CLEAN can be used to remove non-printable characters you’ve imported. Read More …

Different count functions in Excel

COUNT returns the amount of cells in the range which contain a number. COUNTA (count all) returns the amount of cells containing any value. COUNTBLANK returns the amount of cells containing no value. COUNTIF returns the amount of cells in the range which meet your chosen criteria.  

Excel HYPERLINK function

Excel has a built-in function allowing you to add hyperlinks to a cell. You can also give the hyperlink a user-friendly name rather than just displaying the file path.   E.g. =HYPERLINK(“E:\Documents\Finance\Financial Calendar.xlsx”,”Finance calendar”)