[PDF]Excel Formula Book

EXCEL Formulas Bible
Excel 2013 / 2016


Table of Contents


SUM of Digits when cell Contains all Numbers

SUM of Digits when cell Contains Numbers and non Numbers both
A List is Unique or Not (Whether it has duplicates)

Count No. of Unique Values

Count No. of Unique Values Conditionally

Add Month to or Subtract Month from a Given Date

Add Year to or Subtract Year from a Given Date


Convert a Number to a Month Name


DN 0 SN FY Oe WON


Converting Date to a Calendar Quarter
Converting Date to a Indian Financial Year Quarter
Calculate Age from Given Birthday


Number to Date Format Conversion


Count Cells Starting (or Ending) with a particular String
Count No. of Cells Having Numbers Only

Count No. of Cells which are containing only Characters
Number of Characters in a String without considering blanks
Number of times a character appears in a string

Count Non Numbers in a String

Count Numbers in a String

Count only Alphabets in a String

Most Frequently Occurring Value in a Range

COUNTIF on Filtered List

SUMIF on Filtered List


Remove Middle Name in Full Name

Extract Integer and Decimal Portion of a Number

First Day of the Month for a Given Date

How Many Mondays or any other Day of the Week between 2 Dates
Maximum Times a Particular Entry Appears Consecutively

Find the Next Week of the Day

Find the Previous Week of the Day


Get File Name through Formula

Get Workbook Name through Formula

Get Sheet Name through Formula

Get Workbook's Directory from Formula

Last Day of the Month for a Given Date

Perform Multi Column VLOOKUP

VLOOKUP from Right to Left

Case Sensitive VLOOKUP

Rank within the Groups

Remove Alphabets from a String

Remove numbers from string

Roman Representation of Numbers

Sum Bottom N Values in a Range

Sum Every Nth Row

We have AVERAGEIF. What about MEDIANIF and MODEIF? wo. eesssessesesstessseeesneeeesseeeees 16
Number of Days in a Month

How to Know if a Year is a Leap Year

Last Working Day of the Month If a Date is Given
First Working Day of the Month if a Date is Given
Date for Nth Day of the Year

Calculate Geometric Mean by Ignoring 0 and Negative Values


Financial Function - Calculate EMI


Financial Function - Calculate Principal Part of an EMI

Financial Function - Calculate Number of EMIs to Pay Up a Loan
Financial Function - Calculate Interest Rate

Financial Function - Calculate Compounded Interest

Financial Function - Calculate Effective Interest

Abbreviate Given Names

Get Column Name for a Column Number

Get Column Range for a Column Number

Find the nth Largest Number when there are duplicates

Extract Date and Time from Date Timestamp


Convert a Number into Years and Months


COUNTIF for non-contiguous range


Count the Number of Words ina Cell / Range


Numerology Sum of the Digits aka Sum the Digits till the result is a single digit


Generate Sequential Numbers and Repeat them


Repeat a Number and Increment and Repeat


Generate Non Repeating Random Numbers through Formula
Financial Year Formula (e.g. 2015-16 or FY16)

First Working Day of the Year

Last Working Day of the Year


Convert from Excel Date (Gregorian Date) to Julian Date
Convert from Julian Dates to Excel (Gregorian) Dates


Extract User Name from an E Mail ID


Location of First Number in a String

Location of Last Number in a String

Find the Value of First Non Blank Cell in a Range
Find First Numeric Value in a Range

Find Last Numeric Value in a Range

Find First non Numeric Value in a Range

Find Last non Numeric Value in a Range


Find Last Used Value in a Range


1. SUM of Digits when cell Contains all Numbers


If you cell contains only numbers like A1:= 7654045, then following formula can be used to
find sum of digits


=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))


SUM of Digits when cell Contains Numbers and non
Numbers both


If you cell contains non numbers apart from numbers like A1:= 76$5a4b045%d, then
following formula can be used to find sum of digits


=SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:9),"")))*ROW(1:9))


The above formula can be used even if contains all numbers as well.


A List is Unique or Not (Whether it has duplicates)
Assuming, your listisin A1 to A1000. Use following formula to know if list is unique.
=MAX(FREQUENCY(A1:A1000,A1:A1000))
=MAX(INDEX(COUNTIF(A1:A1000,A1:A1000),,))


If answer is 1, then it is Unique. If answer is more than 1, it is not unique.


Count No. of Unique Values
Use following formula to count no. of unique values -


=SUMPRODUCT((A1:A100<>"") /COUNTIF(A1:A100,A1:A100&""))


Count No. of Unique Values Conditionally


If you have data like below and you want to find the unique count for Region = “A”, then you
can use below Array formula -


=SUM(IF(FREQUENCY(IF(A2:A20<>"",IF(A2:A20="A",MATCH(B2:B20,B2:B20,0))),ROW(A
2:A20)-ROW(A2)+1),1))


If you have more number of conditions, the same can be built after A2:A20 = “A”.


Note - Array Formula is not entered by pressing ENTER after entering your formula but by
pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after
pasting and 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.


O eforexcel.com Page 1 of 38


Excel Formulas Bible


B
Sales Man


W00000000>>D>Dp>DOOO
ojojajalsjo|s|3jo |a [9 [ejej jo |o


RRA
IA HBWNHNPOMANDUNFWN HF


Add Month to or Subtract Month from a Given Date


Very often, you will have business problems where you have to add or subtract month
from a given date. One scenario is calculation for EMI Date.


Say, you have a date of 10/22/14 (MM/DD/YY) in A1 and you want to add number of
months which is contained in Cell B1.


The formula in this case would be

=EDATE(A1,B1)

[Secondary formula =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) ]
Now, you want to subtract month which is contained in Cell B1.
=EDATE(A1,-B1)


[Secondary formula =DATE(YEAR(A1),MONTH(A1)-B1,DAY(A1)) ]


Add Year to or Subtract Year from a Given Date


In many business problems, you might encounter situations where you will need to add or
subtract years from a given date.


Let's say A1 contains Date and B1 contains numbers of years.
If you want to add Years to a given date, formulas would be -
=EDATE(A1,12*B1)


=DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))


O eforexcel.com Page 2 of 38


Excel Formulas Bible


If you want to subtract Years from a given date, formulas would be -
=EDATE(A1,-12*B1)


=DATE(YEAR(A1)-B1,MONTH(A1),DAY(A1))


Convert a Number to a Month Name
Use below formula to generate named 3 lettered month like Jan, Feb....Dec
=TEXT(A1*30,"mmm"


Replace "mmm" with "mmmm" to generate full name of the month like January,
February....December in any of the formulas in this post.


Converting Date to a Calendar Quarter


Assuming date is in Cell A1. You want to convert it into a quarter (1, 2, 3 & 4). Jan to Mar is
1, Apr to Jun is 2, Jul to Sep is 3 and Oct to Dec is 4.


=CEILING(MONTH(A1)/3,1)
OR

= ROUNDUP(MONTH(A1)/3,0)
OR


=CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3, 4,4,4)


10. Converting Date to a Indian Financial Year Quarter


Assuming date is in Cell A1. You want to convert it into a Indian Financial Year Quarter. Jan
to Mar is 4, Apr to Jun is 1, Jul to Sep is 2 and Oct to Dec is 3.


=CEILING(MONTH(A1)/3,1)+IF(MONTH(A1)<=3,3,-1)
OR
=ROUNDUP(MONTH(A1)/3,0)+IF(MONTH(A1)<=3,3,-1)
OR


=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)


© eforexcel.com Page 3 of 38


11. Calculate Age from Given Birthday


=DATEDIF(A1,TODAY(),"y")&" Years "&DATEDIF(A1,TODAY(),"ym")&" Months
"&DATEDIF(A1, TODAY(),"md")&" Days"


12. Number to Date Format Conversion


If you have numbers like 010216 and you want to convert this to date format, then the
following formula can be used


=--TEXT(A1,"00\/00\/00") for 2 digits year
Note - Minimum 5 digits are needed for above formula to work


If you have numbers like 01022016 and you want to convert this to date format, then the
following formula can be used


=--TEXT(A1,"00\/00\/0000") for 4 digits year


Note - Minimum 7 digits are needed for above formula to work


13. Number to Time Format Conversion


If you have numbers like 1215 and you want to convert this to hh:mm format, then the
following formula can be used


=--TEXT(A1,"00\:00")

Note - Minimum 3 digits are needed for above formula to work
To convert to hh:mm:ss format

=--TEXT(A1,"00\:00\:00")


Note - Minimum 5 digits are needed for above formula to work


14. Count Cells Starting (or Ending) with a particular
String


1. Say you want to count all cells starting with C
=COUNTIF(A1:A10,"c*")

c* is case insensitive. Hence, it will count cells starting with both c or C.
Suppose you want to find all cells starting with Excel.


=COUNTIF(A1:A10,"excel*")


O eforexcel.com Page 4 0f38


Excel Formulas Bible


2. For ending

=COUNTIF(A1:A10,"*c")

c* is case insensitive. Hence, it will count cells starting with both c or C.
Suppose you want to find all cells starting with Excel.


=COUNTIF(A1:A10,"*excel")


15. Count No. of Cells Having Numbers Only
COUNT function counts only those cells which are having numbers.
Assuming your range is A1:A10, use following formula
=COUNT(A1:A10)


16. Count No. of Cells which are containing only
Characters


Hence, if your cell is having a number 2.23, it will not be counted as it is a number.
Use below formula considering your range is A1:A10


=COUNTIF(A1:A10,"*")


17. Number of Characters in a String without considering
blanks


Say, you have a string like Vijay A. Verma and I need to know how many characters it has.
In this case, it has 12 including decimal and leaving blanks aside.


Use below formula for the same -


=LEN(SUBSTITUTE(A1," ",""))


18. Number of times a character appears in a string


aan


Suppose you want to count the number of times, character “a” appears in a string


=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))


19. Count Non Numbers in a String


Suppose you have a string "abc123def45cd" and you want to count non numbers in this.


If your string is in A1, use following formula in A1


© eforexcel.com Page 5 of 38


Excel Formulas Bible


=IF(LEN(TRIM(A1))=0,0,SUMPRODUCT(--NOT(ISNUMBER((--
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))


20. Count Numbers in a String
Suppose you have a string "abc123def43cd" and you want to count numbers in this.
If your string is in A1, use following formula -
=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))
OR


=SUMPRODUCT(--ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))


21. Count only Alphabets in a String


Suppose you have a string "Ab?gh123def%h*" and you want to count only Aphabets.
Suppose your string is in A1, put following formula for this.


=SUMPRODUCT(LEN(A1)-
LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))


OR


=SUMPRODUCT(--(ABS(77.5-
CODE(MID(UPPER(A1), ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))


22. Most Frequently Occurring Value in a Range


Assuming, your range is A1:A10, enter the below formula as Array Formula 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.
=INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNTIF(A1:A10,A1:A10),0))


The non-Array version of above formula


=INDEX(A1:A10,MATCH(MAX(INDEX(COUNTIF(A1:A10,A1:A10),,)), INDEX(COUNTIF(A1:
A10,A1:A10),,),0))


23. COUNTIF on Filtered List


You can use SUBTOTAL to perform COUNT on a filtered list but COUNTIF can not be done
on a filtered list. Below formula can be used to perform COUNTIF on a filtered list


=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,R0W(B2:B20)-ROW(B2),))*(B2:B20>14))


O eforexcel.com Page 6 of 38


Excel Formulas Bible


Here B2:B20>14 is like a criterion in COUNTIF (=COUNTIF(B2:B20,">14"))


24. SUMIF on Filtered List


You can use SUBTOTAL to perform SUM on a filtered list but SUMIF can not be done on a
filtered list. Below formula can be used to perform SUMIF on a filtered list


=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B20)-ROW(B2),))*(B2:B20>14))


Here B2:B20>14 is like a criterion in SUMIF.
25. Extract First Name from Full Name
=LEFT(A1,FIND(" ",A1&" ")-1)


26. Extract Last Name from Full Name


=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" " LEN(A1))),LEN(A1)))


27. Extract the Initial of Middle Name


Suppose, you have a name John Doe Smith and you want to show D as middle initial.
Assuming, your data is in A1, you may use following formula


=IF(COUNTIF(A1,"* * *"), MID(A1,FIND(" ",A1)+1,1),"")


If name is of 2 or 1 words, the result will be blank. This works on 3 words name only as
middle can be decided only for 3 words name.


28. Extract Middle Name from Full Name


=IF(COUNTIF(A1,"* * *"), MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-(FIND("
",A1)+1)),"")


=1F(COUNTIF(A1,"* * *"), TRIM(MID(SUBSTITUTE(A1," ",REPT("",LEN(A1)),2),FIND("
" A1)+1,LEN(A1))),"")


=IF(COUNTIF(A1,"* * *"), LEFT(REPLACE(A1,1,FIND(" ",A1),""), FIND("
" REPLACE(A1,1,FIND(" ",A1),""))-1))


29. Remove Middle Name in Full Name


=IF(COUNTIF(A1,"* * *"),LEFT(ALFIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1,"
"REPT("",LEN(A1))),LEN(A1))),"")


=IF(COUNTIF(A1,"* * *"), REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND("
AD,


O eforexcel.com Page 7 0f38


Excel Formulas Bible


30. Extract Integer and Decimal Portion of a Number


To extract Integer portion, one of the below can be used -

=INT(A1)

=TRUNC(A1)

Positive value in A1 - If A1 contains 84.65, then answer would be 84.
Negative value in A1 - If A1 contains -24.39, then answer would be -24.
If you want only +ve value whether value in A1 is -ve or +ve, the formula can have many
variants.

=INT(A1)*SIGN(A1) OR =TRUNC(A1)*SIGN(A1)

=INT(ABS(A1)) OR =TRUNC(ABS(A1))

=ABS(INT(A1)) OR = ABS(TRUNC(A1))

To extract Decimal portion -

=MOD(ABS(A1),1)

=ABS(A1)-INT(ABS(A1))


Positive value in A1 - If A1 contains 84.65, then answer would be 0.65.
Negative value in A1 - If A1 contains -24.39, then answer would be 0.39.


31. First Day of the Month for a Given Date


Suppose you have been given a date say 10/22/14 (MM/DD/YY) and you want to calculate
the first day of the Current Month. Hence, you want to achieve a result of 10/1/2014


(MM/DD/YY).
The formulas to be used -
=DATE(YEAR(A1),MONTH(A1),1)
=A1-DAY(A1)+1
=EOMONTH(A1,-1)+1

32. How Many Mondays or any other Day of the Week
between 2 Dates


Suppose A1 = 23-Jan-16 and A2 = 10-Nov-16. To find number of Mondays between these
two dates


© eforexcel.com Page 8 of 38


=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Mon"))
>>>