This blog post looks at using the IF function to display a symbol conditionally in a cell. In the image below a thumbs up or thumbs down symbol is shown dependent upon whether the sale of products have improved since last month.
This tutorial will show you how to display any symbol though, so you could insert a smiley face, hour glass, aeroplane and much more.
This tutorial explains an Excel formula to find the least frequent value in a list. This formula will work whether the value is a number, or text. In this example we want to return the name that occurs the least.
The spreadsheet below shows a list of names with the answer in cell D2. Ross is the name that occurs the least in that list.
This formula returns the least frequent value from the list in A2:A16. The formula is explained below so keep reading.
{=INDEX(A2:A16,MATCH(MIN(COUNTIF(A2:A16,A2:A16)),COUNTIF(A2:A16,A2:A16),0))}
[Read more…] about Excel Formula to Find the Least Frequent ValueYou may need to count the occurrences of a specific weekday between two dates in Excel. For example, how many Fridays between two dates.
There is no real standalone function in Excel to do this, but it can be done. This could be a useful formula to find how many payments, or how many meetings until an end date.
The formula below compares the dates in cells A2 and B2 and returns the the number of Fridays between them. The formula is explained below.
=B2-A2-NETWORKDAYS.INTL(A2,B2,16)+1
In this formula, the start date is subtracted from the end date. This leaves us with how many days in total between the two dates.
In the second half of the formula, the NETWORKDAYS.INTL function is used to calculate the difference between the two dates excluding Fridays. This is then subtracted from the current total to leave us with how many Fridays there are.
The 16 in the function specifies to exclude the Fridays. When typing the function a list appears asking which days to exclude in the weekend argument.
The +1 is added to the end because the NETWORKDAYS.INTL function calculates whole workdays. For example, you may consider the difference in days between today and tomorrow to be 1 day. NETWORKDAYS.INTL would return that answer as 2 as it uses each day as a whole day. So the +1 is added to counteract that.
[Read more…] about Count How Many Fridays Between Two DatesThis blog post looks at using an Excel formula to display the sheet name in cell. By finding the sheet name using an Excel formula, it ensures that if the sheet name is changed, the formula returns the new sheet tab name.
This tutorial covers two Excel formula examples. Firstly, using the CELL, MID and FIND functions. And then using the TEXTAFTER function with CELL.
Let’s begin by looking at the CELL function.
[Read more…] about Excel Formula to Display the Sheet Name in CellVLOOKUP is an awesome Excel function. One of its limitations is that it can only return the first match from a list. In this tutorial, we will see how to use VLOOKUP for the last match in Excel.
VLOOKUP is typically used to look for a unique value. But what about when the value you are looking for appears multiple times in the list, and you want to return the last match.
Sure we could sort the list so that the last match would become the first, but this is not always an option.
This tutorial uses VLOOKUP for the last match. The formula used can be adapted to find the 2nd or 3rd match, if required.
