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…
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…
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…
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…
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…
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…
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
Tips & Tricks 71 – Sum Every Odd Row
If your range is A1:A100, use following formula. =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)<>0)) This is a generic formula, hence if your range is B7:B50, your formula will become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)<>0))
Tips & Tricks 70 – Sum Every Even Row
If your range is A1:A100, use following formula. =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)) This is a generic formula, hence if your range is B7:B50, your formula will become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))