Knowledge base

You can use one of Excel's text formatting formulas to identify whether the credit or debit matches the numbers in the transaction narrative exactly. An example to get you started with the necessary formulas is shown here: =IF( ISNUMBER( SEARCH( TEXT(B3,"0.00"),A3 ) ),"yes","no" ) Where, B3 contains the amount and A3 contains the narrative. #Formula #Excel #All

Some text manipulation can be used to cut out the sections of the narrative that interest you. This formula looks for 'from' or 'to' and displays the text after these keywords. If neither word exist, the whole narrative is displayed. =IF(ISNUMBER(SEARCH("from",INDIRECT("RC[-1]",FALSE))), MID(INDIRECT("RC[-1]",FALSE),SEARCH("from",INDIRECT("RC[-1]",FALSE))+LEN("from")+1,LEN(INDIRECT("RC[-1]",FALSE))), IF(ISNUMBER(SEARCH("to",INDIRECT("RC[-1]",FALSE))), MID(INDIRECT("RC[-1]",

When Excel reads a statement that returns TRUE or FALSE, these values are interpreted by the formula as 1 or 0, respectively. Therefore, these two statements return the same result: =IF ( ISNUMBER (A1) , B1 , 0 ) =B1 * ISNUMBER (A1) This can be a useful trick to improve the readability and speed of writing some formulae. #Formula #Excel #All

When you copy and paste formulae that reference other cells within a spreadsheet, the cell references are automatically interpreted as being relative to the original formula location. To stop the location of the referenced cell changing when it is copied elsewhere, you should insert $ signs before the column or row (or both). For example, if =$A1 was copied horizontally, the column would stay the same. If the same formula was copied vertically, the row would change. #Formul

If you have a list of many company names (perhaps creditors or debtors) and you need to check which ones are dissolved or subject to Liquidation/Administration proceedings then you should use Companies House public data (download.companieshouse.gov.uk/en_output.html). By downloading and uncompressing the zip files provided you will be able to open the data in Excel. Then you can use a VLOOKUP formula on the company name to obtain the company status for each company in your o

Balance checks are used to validate that the data exported from bank statements is correct. They are quick and easy to apply, although there are a couple of points that you need to be careful of: - Is the balance calculated before or after the transaction on the same line? - Are there spaces or text within the debit, credit of balance columns? - Does the page date order match the transaction date order (in other words, does the balance flow from one page to the next? The abo

SUMIF can be used to summarise many transactions for a single condition. Here are some examples: - Show the total credits where the date is after 1 January 2013 - Show the total credits where the balance was overdrawn - Show the total credits where the description includes the word 'HMRC' Although this may seem useful at first, you will quickly want to summarise data based on more than one condition (for example, what's the total credits for the period between 1 January 2013

SUMPRODUCT is a powerful formula that can be used to summarise transactions that satisfy many different conditions. Here are some examples: - Show the total credits where the date is after 1 January 2013 and before 31 December 2013 - Show the total credits where the balance was overdrawn but within the agreed overdraft limit - Show the total credits where the description includes the word 'HMRC' or 'VAT' SUMPRODUCT takes each element within the formula and multiplies each ro

Changing dates across many lines of transactions can be tiresome. To ensure that this process is fast and accurate, you should use the DATE formula. =DATE ( year , month , day ) Using this format you can use certain parts of the date that has been extracted from the bank statements, quickly replacing, for example, the year, with 2014. To achieve this example, use the following format, replacing XXX with a cell reference to the extracted date: =DATE ( 2014 , month(XXX) , day