Avoid Zero Result with ISBLANK

It's common when using formulas like VLOOKUP or IF in Excel to return a value from a cell which is blank - Excel will give a zero result.

In this example, IF formulas are used to fill in the city and store columns for each listed product when the type of product changes in column E. On the left of the worksheet the city and store are only entered at the beginning of the product list and we need to have these values repeat on each row.

However because no store is listed for the products from Chicago, a '0' result is returned.

If you want to avoid a zero in the result and instead have a blank result from the formula, utilize Excel's ISBLANK formula. Use ISBLANK with a nested IF formula and so if the result to be listed is blank, "" - no value is returned.

=IF($D2<>$D1,IF(ISBLANK(C2),"",C2),F1)

Excel formula to check how much two entries match

Here's a way to use formulas in Excel to compare how much entries in two cells match one another - - based on the percentage of characters which match up from the left side of each cell.

1. In this example we have two different spellings for cities listed in column A and column B.

2. In column C we use the LEN formula to get the number of characters used for the version of the city name in column A.

3. In column D enter this formula:

=SUMPRODUCT( --(LEFT(A2, ROW(INDIRECT("A1:A" & C2))) =LEFT(B2, ROW(INDIRECT("A1:A" &C2)))))

This formula is unusual because it must be entered on separate lines. The SUMPRODUCT formula is used to get a result based on the left side of data in column A and column B - taken in conjunction with the LEN result in column C. The resulting number is how many of the characters from the left in column A are matched in column B.

4. In column E, change the format to percentage and divide the formula results in column D by the LEN result.

The percentage shows how much of the left side of the entry in column A matches up with the entry in column B.

Thanks to UniMord for posting this formula here.

Excel DATEDIF formula to show time gap between two dates

You can use the DATEDIF formula to calculate the number of years, months, or days between dates entered in two different cells in an Excel spreadsheet.

So, if we use the formula: =DATEDIF(A2,B2,"Y")

. . . it will show the number of years between one date in cell A2 and a later date in B2. The result will only show the full number of years. So in this example:

. . . only three years are shown between November 8, 2016 and November 3, 2020 because it has been five days short of 4 years.

If the letter in parentheses at the end of the formula is changed from a Y to a M, the number of months will be shown.

=DATEDIF(A3,B3,"M")

Changing to a D will result in the number of full days being shown in the designated date range.

=DATEDIF(A4,B4,"D")