Zero if error or #N/A in Vlookup
Sometimes the result of an excel formula is an element of a larger and sophisticated calculation which will end displaying an error if any of the elements taken into account is not a number. This is the case of #N/A returned by the VLOOKUP function when there is no match to the searched element. The […]
Determining top rankings of amounts in a range
Sometimes we have to know the rankings of amounts in an array or range, in order to select some of them, let’s say top 10. Microsoft Excel comes to the rescue by offering a formula to count elements that have certain attributes or meet some conditions. This function is COUNTIF and it’s result is the […]
Two formated values in the same cell
Sometimes you need to insert two values into the same cell. Like having the profit variation in absolute terms and in percentages in a single cell. Different format for two values in a single cell The solution to this problem is the TEXT(value,format) formula. Imagine that you need to show the variance over two years […]
Format cell to show negative percent values in red
Isn’t it nicer to see negative percent values in red color instead of all black, differentiated only by the brackets or by the (-) sign?! The format code pattern to show negative percent in red color Select the cells you want to format and click on Format cells… button. On the Number tab write in […]
Excel countif formula
Whenever you need to count the elements inside data series the excel countif formula is available to help you reach your target easy and fast. The Countif formula has the following structure: =COUNTIF(Range,Condition) Note that the Range can be a large table not only one column. Examples of Excel Countif Formula Let’s say we need […]
Counting characters excepting spaces using excel formula
The easy way to count the characters in a text, without counting the spaces too, is to use the LEN excel formula on a string text that has the spaces removed. Removing spaces before counting characters Removing spaces inside a text is done by using the SUBSTITUTE formula with the following expression: =SUBSTITUTE(“text where you […]
Automatic sorting using workbook formulas
Optimizing the conclusions presented by an Excel worksheet data processing algorithm usually needs a solution for information sorting in order for it to be displayed top-down by importance factor to the subject. Additional formulas needed You need to insert the formulas to calculate automatically the position of each item in the list based on it’s […]
Count words in Excel using worksheet formulas
The solution for this problem is simple. The words are separated by spaces and these spaces need to be counted in order to find out how many words there are inside the text. Worksheet formula to count words in a text If we eliminate the spaces with the Substitute formula and count how many eliminations […]