## Article 10 - Intersection Operator in Excel

Today, I am going to talk about a nifty but not so well known feature of Excel. This is about Intersection Operator in Excel. Before, I go deep in Intersection Operator, I would like to talk about Reference Operators in Excel. There are 3 Reference Operators -

**1. Range Operator** (represented by Colon) - It specifies a range. Hence, A1:B10 where a colon (:) has been used, specifies all cells contained between A1 to B10. Similarly, D:D specifies entire column D. 3:3 specifies entire row 3.

**2. Union Operator** (represented by Comma) - It specifies the union of ranges. Union means inclusive of all cells in the ranges specified. Hence, if you specify A1:A5,D2:D4 it will include all cells lying between A1 to A5 and D2 to D4. Hence, if I specify =SUM(A1:A5,D2:D4)

The answer would be 221.

If I specify =SUM(A1:A5,B2,D2:D4,C4,C1:C2), it will pick up all cells from A1 to A5, B2, D2 to D4, C4 and C1 to C2.

The answer would be 326.

## Tips & Tricks 75 - What is DATEDIF and it is not available in my Excel

DATEDIF calculates the difference between two dates in terms of **COMPLETED** Days, Months and Years. Note the emphasis on COMPLETED.

It means that if elapsed years or months or days is 1.78, the answer would be 1 only. Hence, it gives only integer and doesn't round them.

A good documentation exists on following link

http://www.cpearson.com/excel/datedif.aspx

It is available in Excel by default. But it will not be available in Excel help. It will not be available in your list of functions in Excel. So, if you type =Date in Excel in any cell, below options get available -

**But you can go ahead and use this as given on cpearson site. **

## Tips & Tricks 74 - We have AVERAGEIF. What about MEDIANIF and MODEIF?

Excel doesn't provide MEDIANIF and MODEIF. You will have to use Array formulas to achieve these functionality. Let's assume that our data is like below -

To calculate MEDIANIF and MODEIF, enter below formulas i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=MEDIAN(IF(A2:A13="M",B2:B13))

=MODE(IF(A2:A13="M",B2:B13))

**Non-Array alternatives**

**For MEDIANIF**

=AGGREGATE(16,6,(B1:B13)/(A1:A13="m"),50%)

**For MODEIF**

=INDEX(B1:B20,MATCH(MAX(INDEX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,)),INDEX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,),0))

## Challenge 8 - Counting Number of single 1s in Rows in a Grid

Suppose you have a grid like below. The challenge before you is to count number of rows which have only single 1. See the grid, the yellow highlighted ones are containing 1 only once. The answer is 3 for this problem.

You have to give me a single formula for the same. The relevant Excel can be downloaded from Grid

Note - You may choose to post the response in comments section.

## Challenge 7 – Formula for Sum of Square of the Digits in an Alphanumeric String

Last challenge was about giving a non-array formula for Sum of Square of the Digits in a Numeric String. Now, the challenge is for an alphanumeric string. This time, there is no restriction that you need to give only non-array formula only. You may give array formula also.

Let's say cell A1 contains an alphanumeric string. The challenge before you is to give me a formula to sum the square of the digits.

As an example if A1=a3c78q90A6, then answer would be

Note - You may choose to post the response in comments section.

## Article 9 - Overcome WildCard VLOOKUP / MATCH Problem when Target String is more than 255 Characters

Once again, I have got idea to write this article after I responded to this post Vlookup to find URL using wildcard in Excel Microsoft Community.

Before I delve into problem statement, I want to start with some basic information. **Maximum column width** can be of 255 characters, this means that if I select a column, take right click and select Column Width, I can give a maximum value of 255. If I try to give more than 255, it will immediately give an error message.

**Length of cell contents (text)** - 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

## Tips and Tricks 73 - Use Conditional Formatting to Highlight Duplicate Cells

Suppose your data is in range A2:A100 and you want to highlight all those cells which are duplicates.

1. Select A2:A100 and Home Tab > Conditional Formatting > New Rule

2. Put following formula after clicking "Use a formula to determine which cells to format"

=COUNTIF($A$2:$A$100,A2)>1

3, Click on Format Button to format the cells accordingly.

## Tips & Tricks 72 – Sum Every Nth Row

If your numbers are in range A1:A100, use below formula

=SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,**2**)=0))

Above formula is for every 2nd row. Replace 2 with N. Hence, for every 5th row -

=SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,5)=0))

This is a generic formula and will work for any range. If you range is B7:B50, your formula would become

=SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,**2**)=0))

## Excel Quiz 5 - Crossword - I

This time, I decided to put a Crossword on Excel Functions. This crossword is interactive and you can use browser to play with this. Click in the Grid to start.

Click on Check Puzzle just above Questions to check your score.

If you want to print the Crossword on a paper to play with, download from here Excel Functions Crossword 1

The answer key can be downloaded from here Excel Functions Crossword 1 Answers

**If the below Crossword is not properly visible because of WordPress plugin limitation, you can play it properly here Excel Functions Crossword 1**