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")
Use an ampersand instead of the CONCATENATE function. The formula =A1&”-“&B1 is much easier than =CONCATENATE(A1,”-“,B1)
Use this formula with Conditional Formatting to highlight alternating sets of 5 rows: =MOD(CEILING(ROW(),5),2)
Create month-end date series- Cell A1: 10/31/09, Cell A2: =DATE(YEAR(A1),MONTH(A1)+2,0), copy down as needed.
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))