Tag: Formula

AND() Function

The AND function allows you to tests for two or more conditions in an IF statement. =IF(AND(A1>=1,A1<=100),"Between 1-100","Other")

Concatenation Trick

Use an ampersand instead of the CONCATENATE function. The formula =A1&”-“&B1 is much easier than =CONCATENATE(A1,”-“,B1)

Formatting Sets Of Rows

Use this formula with Conditional Formatting to highlight alternating sets of 5 rows: =MOD(CEILING(ROW(),5),2)

Create A Series Of Month End Dates

Create month-end date series- Cell A1: 10/31/09, Cell A2: =DATE(YEAR(A1),MONTH(A1)+2,0), copy down as needed.

Date Series Formula

This formula allows you to create a series of dates that alternate between the 15th and the last day of each month: =IF(B1=DATE(YEAR(B1),MONTH(B1)+1,0),DATE(YEAR(B1),MONTH(B1)+1,15),DATE(YEAR(B1),MONTH(B1)+1,0))