Let's say that A1 contains the year. To know whether it is a Leap Year or not, use following formula – =MONTH(DATE(A1,2,29))=2 TRUE means that it is Leap Year and FALSE means that this is not a Leap Year.
Category: Tips and Tricks
Tips & Tricks 78 – Number of Days in a Month
Suppose, you have been given a date say 15-Nov-14 and you have to determine how many days this particular month contains. The formula which you need to use in the above case would be =DAY(EOMONTH(A1,0)) Explanation – EOMONTH(A1,0) gives the last date of the month and DAY function extract that particular Day from the last…
Tips & Tricks 77 – Inserting picture in the comments box
The below discussion is for Excel 2013. If a cell is selected and it has comments, the comments will be displayed. Sometimes, there may be a need to display picture when you select a cell. I answered this question on Microsoft Community Forum and I have decided to reproduce it here. 1. Right click the…
Tips & Tricks 76 – Whenever I Refresh a Pivot, its Column Width Changes
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.
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…
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…
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))
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))