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.
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…
Challenge 9 – Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet
Given the below table, the challenge before you is to – Work out a formula for Customer spending Maximum amount and his Spend (Cells H4 and H5) for a given Month (H2) and starting alphabet (H3). Cell H2 has the month in numbers 1 to 12 and H3 has starting alphabet. If H2 is blank,…
Article 11 – How to Encrypt Excel Workbook
Note – The article is for Excel 2010 / 2013 versions and doesn't cover any version prior to Excel 2010. Excel 2010 / 2013 uses default 128 bit AES encryption which is considered highly secured. If you are interested in knowing all things about Excel security, I will recommend following MS article – http://technet.microsoft.com/en-us/library/dn194021%28v=office.15%29.aspx —-…
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…
Excel Quiz 6
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.
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…
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…