Article 12 – Overcoming Wildcard Limitation in Sumproduct
In my view, SUMPRODUCT is the king of all Excel Functions and till Excel 2003, it was rightly so. But starting Excel 2007, SUMIF(S) and COUNTIF(S) conspired to dethrone the king named SUMPRODUCT. But once a king, always a king. There are many scenarios where SUMPRODUCT still comes handy and this is still one of…
Tips & Tricks 82 – Date for Nth Day of the Year
Suppose A1 contains the Year and you are asked to find 69th day of the year which is contained in A2. Then formula for finding Nth day of the year would be =DATE(A1,1,1)+A2-1
Tips & Tricks 81 – First Working Day of the Month if a Date is Given
If A1 contains a date, then formula for First Working Day of the month would be =WORKDAY(EOMONTH(A1,-1),1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula – =WORKDAY.INTL(EOMONTH(A1,-1),1,"0000110") Where 0000110 is a 7 character string, 1 represents a…
Excel Quiz 7
Tips & Tricks 80 – Last Working Day of the Month If a Date is Given
If A1 holds a date, the formula for calculating last Working Day of the month would be =WORKDAY(EOMONTH(A1,0)+1,-1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula – =WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"0000110") Where 0000110 is a 7 character string, 1 represents…
Tips & Tricks 79 – How to Know if a Year is a Leap Year
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 —-…