Despite having used Excel for over 10 years, I only came across the Intersect operator when someone at work asked me if it was possible to sum the numbers that intersected in a matrix of values. A quick search on Excel's Help provided the answer.
An operator is a symbol that tells Excel what calculation to perform. You will all be familiar with operators such as + / - and * but Excel has an operator that specifies the cells that intersect 2 (or more) ranges - and that operator is a single space.
EXAMPLE: The intersection of the ranges A1:C3 and B1:B6, would be the range B1:B3, but rather then having to specify that range, you can get Excel to perform a calculation on the intersection by specifying the 2 ranges as follows:
=SUM(A1:C3 B1:B6) [note the single space operator between the 2 ranges]
You are not limited to the SUM function, any function can be used.
It can often be useful to generate a specific date based on the current system date. The following formulas do just that:
NB: All dates are shown in the UK format of dd/mm/yyyy. Apologies to any Americans or other foreigners who might find this confusing, but I can't get my head around the way you put the month before the day - just seems arse about face to me.
| System Date | =TODAY() |
| First of CURRENT month | =TODAY()-DAY(TODAY())+1 |
| Last of Current month | =EDATE(TODAY(),1)-DAY(EDATE(TODAY(),1)) |
| First of PREVIOUS month | =TODAY()-DAY(TODAY())-DAY(TODAY()-DAY(TODAY()))+1 |
| Last of PREVIOUS month | =TODAY()-DAY(TODAY()) |
| First of NEXT month | =EDATE(TODAY(),1)-DAY(TODAY())+1 |
| Last of NEXT month | =EDATE(TODAY(),2)-DAY(EDATE(TODAY(),1)) |
Whilst it is not directly possible to specify more than one criterion when using the COUNTIF formula, there is a workaround using an Array Formula.
NB: To accept any Array Formula, you MUST press Ctrl+Shift+Enter. Simply pressing Enter will resut in either an incorrect value or a #NUM! error. Because of this different way of accepting the formula, Array formulas are often called CSE formulas.
Assume that each cell in the range A2:A11 contains a number between 1 and 9. You need to count the number of entries that have a reference between 1 and2, between 3 and 4, between 5 and 6 & between 7 and 9. The formula is:
=SUM((A2:A11>=1)*(A2:A11<=2))
Using the same assumption as above, but this time Column B contains the values we want to SUM the formula is:
=SUM((A2:A11>=1)*(A2:A11<=2)*(B2:B11))
AVERAGEIF - There is no such function in Excel, but using the Array formula it's as easy as pie:
=AVERAGE(IF(A2:A11="East",C2:C11))
This would give you the average of all values in Column C where the corresponding value in Column A is East. Very useful
MAXLEN - I've recently had to check a spreadsheet that contains data input by another Department that will eventually be transferred into a SQL database. Certain columns in the spreadsheet can only contain 16 characters for when the data is transferred into the SQL table, and as the users didn't set up the spreadsheet with Data Validation, the spreadsheet (which runs to several thousand rows) could contain data that's more than 16 characters in length.
Now, I could type in a formula to calculate the length of the text in Cell A1 =LEN(A1) and then copy that down for how ever many rows of data I have, and then create another formula to return the highest value from all the other formulas =MAX(A1:A2000), but that would mean having several thousand formulas on the spreadsheet.
The same result can be obtained with a single Array formula as follows:
=MAX(LEN(A1:A2000))
See how powerful this is? Just remember that to accept an Array formula, you must press CSE (Ctrl+Shift+Enter)