Sat 31 Jan 2015

Tips & Tricks 76 - Whenever I Refresh a Pivot, its Column Width Changes

By |Saturday, January 31st, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

You can stop Pivot from doing this behaviour. Take following steps -

1. Right Click on Pivot Table > PivotTable Options
2. Uncheck Autofit column widths on update.

1

Sat 31 Jan 2015

Article 10 - Intersection Operator in Excel

By |Saturday, January 31st, 2015|Categories: Articles|Tags: , , , , |0 Comments

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.

(more…)

Sat 24 Jan 2015

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

By |Saturday, January 24th, 2015|Categories: Tips and Tricks|Tags: , |0 Comments

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 -

1

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

Sat 17 Jan 2015

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

By |Saturday, January 17th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

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 -

1

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))

Sat 17 Jan 2015

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

By |Saturday, January 17th, 2015|Categories: Challenges|Tags: , , , , |1 Comment

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

1

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

Sat 10 Jan 2015

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

By |Saturday, January 10th, 2015|Categories: Challenges|Tags: , , , |1 Comment

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

1 = 9+49+64+81+0+36 = 239

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

Sat 10 Jan 2015

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

By |Saturday, January 10th, 2015|Categories: Articles|Tags: , , , , |2 Comments

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.

(more…)

Sat 10 Jan 2015

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

By |Saturday, January 10th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

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

1

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

Sat 03 Jan 2015

Tips & Tricks 72 – Sum Every Nth Row

By |Saturday, January 03rd, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

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))

Sat 03 Jan 2015

Excel Quiz 5 - Crossword - I

By |Saturday, January 03rd, 2015|Categories: Quizzes|Tags: , , , |3 Comments

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

(more…)