Total cells in a range

Written by kazamraza no comments
Classified in : Excel Formulas Tags : none

In some cases we need to calculate total number of cells in a range. The COUNT,COUNTA and COUNTBLANK Functions are different in nature.

We use ROWS formula to calculate total number of cells in a range.

For example we want to calculate total number of cells in range B5:B23, we can use formula:

Read more Total cells in a range

DATE Function

Written by kazamraza no comments
Classified in : Excel Formulas, Date & Time Functions Tags : none

If you find a data with date column in which date has been stored as shown below

05.06.2010     it is read as June 05, 2010, but it can also be read as May 06, 2010. The correct one is June 05, 2010 according to my regional settings.

If you replace the full stop sign with slash sign,it is possible that the date is converted to May 06,2010 which is incorrect answer.

In this scenario you can use DATE Function along with LEFT, RIGHT and MID Function to solve the problem.

Read more DATE Function

CHAR Function

Written by kazamraza no comments
Classified in : Excel Formulas, Text Functions Tags : none

This function is not in common use. However advance users of Excel use this function. For example if there is a Tab character in cell content, then it is very difficult to find it in normal excel working. I can easily find and replace it by using SUBSTITUTE Function and CHAR Function. For example if there is Tab character in kazamraza, you can not see it visually, you can replace Tab character with a space by using the following formula.


=SUBSTITUTE("Kazam"&CHAR(9)&"Raza",CHAR(9)," ")

When you export data from some software, there may be a chance that Tab and other non-printable characters are in the export file.

The CHAR function returns the character specified by a number.

Read more CHAR Function