Always Refer to Cell A1

If you need to ALWAYS refer to cell A1, regardless of whether row or columns are inserted or deleted, then use the following formula.

=INDIRECT("A1")

This will always display the entry in cell A1 on the current sheet.

Another formula that always refers to cell A1 on the current sheet is

=INDEX(1:1048576,1,1)


Days in Month Formula

If you need to calculate how many days in a month, you can use two functions together.

Assuming cell A1 has a date within the month, you can use

=DAY(EOMONTH(A1,0))

 

The Problem with the MROUND Function

The ROUND function rounds values to decimal places on either side of the decimal point. It is useful and popular. The MROUND function is meant to allow you more flexibility in your rounding calculations. Let’s say you want round to closest 0.05. The MROUND is meant to handle this calculation but unfortunately it provides inconsistent results.

Random Dates

In Excel dates are stored as numbers.

To generate random dates you can use the RANDBETWEEN function with a start date and an end date.

random DateThe formula in cell C2 is

=RANDBETWEEN(A2,B2)

Cell C2 has been formatted as a date.

This formula is dynamic. Each time Excel calculates it will update and most likely change.

You can use Copy > Paste Values to capture the date(s).

 

 

Counting Errors in a Range in Excel

If you need to ensure that a range has no errors you can count the errors and compare the result to zero to ensure the range is error free.

count errors example

The formula in cell C1 which counts errors in a range is

=SUMPRODUCT(ISERROR(A1:A5)*1)

If you want to display TRUE for no errors and FALSE for error(s) you can use

=SUMPRODUCT(ISERROR(A1:A5)*1)=0

You can also use an IF function to display the text Error if any errors are found.

=IF(SUMPRODUCT(ISERROR(A1:A5)*1)=0,"OK","Error")

 

 

Removing Line Feeds in Excel

Sometimes when text data is imported from other systems it can contain line feeds.

A line feed wraps the text onto a new line – it is not associated with the Wrap Text format.

Cell A1 below shows an example. Cell B1 has the formula that removes line feeds ans replaces it with a space.

line feed example

The formula in B1 is

=SUBSTITUTE(A1,CHAR(10)," ")