The goal of this example is to verify input before calculating a result. The key point to understand is that any valid formula can be substituted. The SUM function is used only as an example. The logic can also be...
First, the LEN function counts total characters in the cell B5.
Next SUBSTITUTE removes all "line returns" from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows....
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. So, in this case the formula =ISBLANK(B4) is evaluated for...
In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:
=IF(ISBLANK(C5),"",COUNTA($C$5:C5))
The IF function first checks if cell C5 has...
So, the gist of the problem is this: We want to get the first non-blank cell, but we don't have a direct way to do that in Excel. We could use VLOOKUP with a wildcard * (see link below), but that will only work for...
The logical expression ="" means "is empty". In the example shown, column D contains a date if a task has been completed. In column E, a formula checks for blank cells in column D. If a cell is blank, the result is a...
This formula is based on the IF function, configured with a simple logical test, a value to return when the test is TRUE, and a value to return when the test is FALSE. In plain English: if Value 1 equals 1, return Value...
The value in G5 is hard-coded. The formula picks up the value in G5, then subtracts the value (if any) in E6 and adds the value (if any) in F6. When the credit or debit values are empty, they behave like zero and have...
This formula uses the IF function to check for "complete" in column C. When a cell contains "complete", IF returns
CHAR(252)
which displays a checkmark when the font is "Wingdings". When a cell contains any other...
In Excel, empty double quotes ("") mean empty string. The symbol is a logical operator that means "not equal to", so the following expression means "A1 is not empty":
=A1<>"" // A1 is not empty
This...
In this example, column D records the date a task was completed. Therefore, if the column contains a date (i.e. is not blank), we can assume the task is complete.
The formula in cell E5 uses the IF function to check...