Search for notes by fellow students, in your own course and all over the country.
Browse our notes for titles which look like what you need, you can preview any of the notes via a sample of the contents. After you're happy these are the notes you're after simply pop them into your shopping cart.
Title: Microsoft 365 Excel formulas
Description: Microsoft 365 Excel formulas are powerful tools that help users efficiently analyze and manipulate data. With a wide range of functions—such as SUM, VLOOKUP, IF, and INDEX—Excel formulas automate complex calculations, streamline workflows, and provide valuable insights from large datasets. Whether you're managing budgets, tracking performance, or generating reports, Excel formulas are essential for boosting productivity and making data-driven decisions with ease.
Description: Microsoft 365 Excel formulas are powerful tools that help users efficiently analyze and manipulate data. With a wide range of functions—such as SUM, VLOOKUP, IF, and INDEX—Excel formulas automate complex calculations, streamline workflows, and provide valuable insights from large datasets. Whether you're managing budgets, tracking performance, or generating reports, Excel formulas are essential for boosting productivity and making data-driven decisions with ease.
Document Preview
Extracts from the notes are below, to see the PDF you'll receive please use the links above
EXCEL Formulas
Excel 365
Table of Contents
1
...
2
2
...
2
3
...
2
4
...
of Unique Values
...
Count No
...
2
6
...
,Sat
...
Generate Sequential Month names like Jan, Feb, Mar
...
4
8
...
4
9
...
4
10
...
4
11
...
5
12
...
5
13
...
5
14
...
6
15
...
6
16
...
6
17
...
g
...
7
18
...
7
19
...
7
20
...
8
21
...
8
22
...
8
23
...
9
24
...
9
25
...
10
26
...
10
27
...
10
28
...
11
29
...
11
30
...
11
31
...
12
32
...
12
33
...
13
34
...
13
35
...
14
36
...
14
37
...
14
38
...
15
39
...
16
40
...
16
41
...
16
42
...
17
43
...
17
44
...
18
45
...
18
46
...
18
47
...
19
48
...
20
49
...
of Cells Having Numbers Only
...
Reverse a String
...
Reverse Number String
...
Reverse a String of Words
...
Reverse a Range of Cells
...
Get First Row Number in a Range
...
Get Last Row Number in a Range
...
Get First Column Number in a Range
...
Get Last Column Number in a Range
...
Extract Unique Characters
...
Test whether a range contains only numbers
...
Test whether a range contains only English alphabets
...
Count No
...
23
62
...
23
63
...
23
64
...
23
65
...
24
66
...
24
67
...
24
68
...
25
69
...
25
70
...
25
71
...
25
72
...
26
73
...
26
74
...
27
75
...
27
76
...
28
77
...
28
78
...
28
79
...
28
80
...
28
81
...
28
82
...
29
83
...
29
84
...
29
85
...
30
86
...
30
87
...
30
88
...
30
89
...
31
90
...
31
91
...
32
92
...
33
93
...
33
94
...
34
95
...
34
96
...
34
97
...
34
98
...
35
99
...
35
100
...
36
101
...
36
102
...
36
103
...
37
104
...
What about MEDIANIF and MODEIF?
...
Calculate Geometric Mean by Ignoring 0 and Negative Values
...
Generate GL Codes
...
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
...
COUNTIF for non-contiguous range
...
Count the Number of Words in a 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
...
Extract User Name from an E Mail ID
...
Extract Domain 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
...
I have data for many years but I want the sum for only last 12 months
...
Generate a Unique List out of Duplicate Entries
...
Financial Function - Calculate EMI
...
Financial Function - Calculate Interest Part of an 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
...
Financial Function – Calculate CAGR and AAGR
...
Slab Billing – Calculate Income Tax, Electricity (Utility) Bills based on Slabs
...
LTRIM and RTRIM through Excel Formulas
...
A Note about Array formulas (not for Excel 365 / Excel 2021)
Sometimes, you will need to enter a formula as array formula
...
Only for older versions of Excel, you might need to enter a formula as Array
formula
...
If you are copying and pasting this formula, take F2 after
pasting and CTRL+SHIFT+ENTER
...
If you edit again, you will have to do CTRL+SHIFT+ENTER again
...
Page 1 of 60
Excel Formulas
1
...
2
...
A List is Unique or Not (Whether it has duplicates)
Assuming, your list is in A1 to A1000
...
=MAX(COUNTIF(A1:A1000,A1:A1000))
If answer is 1, then it is Unique
...
4
...
of Unique Values
Use following formula to count no
...
Count No
...
6
...
,Sat
For 3 characters Weekday name
=TEXT(DATE(2017,1,SEQUENCE(7)),"ddd")
=TEXT(DATE(2017,1,ROW($1:$7)),"ddd")
(Note – Year 2017 has been used as 1-Jan-2017 was Sunday
...
Say, if you want to
show 1 = Monday, 2 = Tuesday……
...
Hence, formulas would become
=TEXT(1+DATE(2017,1,SEQUENCE(7)),"ddd")
=TEXT(1+DATE(2017,1,ROW($1:$7)),"ddd")
Page 3 of 60
Excel Formulas
The above formulas will generate the sequence in a column
...
Generate Sequential Month names like Jan, Feb,
Mar
...
To generate in a row –
=TEXT(DATE(1,SEQUENCE(,12),1),"mmm")
=TRANSPOSE(TEXT(DATE(1,ROW($1:$12),1),"mmm"))
8
...
Number of Days in a Month
Suppose, you have been given a date say 15-Nov-21 and you have to determine how many
days this particular month contains
...
Find First Day of the Month
Suppose, you have a date in the cell A1≔ 14-Aug-21, then formula for finding first day of
the month is
Page 4 of 60
Excel Formulas
=A1-DAY(A1)+1
=EOMONTH(A1,-1)+1
=DATE(YEAR(A1),MONTH(A1),1)
11
...
One scenario is calculation for EMI Date
...
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
...
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
...
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))
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))
13
...
Dec
Page 5 of 60
Excel Formulas
=TEXT(A1*30,"mmm")
Replace "mmm" with "mmmm" to generate full name of the month like January,
February
...
14
...
December (or Jan
...
Dec) and you
want to show 1, 2……12
=MONTH("1"&A1)
The formula would work as long as month names are >=3 characters
...
15
...
7 = Saturday
=TEXT(DATE(2017,1,A1),"dddd")
Note – 2017 has been used in above formula as 1-Jan-2017 was Sunday
...
To show only 3 characters of the Weekday Name, replace dddd with ddd
=TEXT(DATE(2017,1,A1),"ddd")
You can add a number to A1 if you want to show some other Weekday Name
Say, if you want to show 1 = Monday, 2 = Tuesday……
...
7 = Thursday, just add 5 to A1
=TEXT(5+DATE(2017,1,A1),"dddd")
16
...
Saturday (or Sun, Mon…
...
7, then following formula can be used to return the numbers
...
=ROUND(SEARCH(LEFT(A1,2),"SuMoTuWeThFrSa")/2,0)
=MATCH(LEFT(A1,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0)
If we want to return some other number to weekdays, then formula can be tweaked
accordingly
...
Financial Year Formula (e
...
2015-16 or FY16)
A good number of countries don't follow calendar year as the financial year
...
Hence, currently (20-Feb-16),
the financial year is 2015-16 (It is also written as FY16)
...
Now if a date is given, then following formula can be used to derive 2015-16 kind of result
...
Converting Date to a Calendar Quarter
Assuming date is in Cell A1
...
Jan to Mar is
1, Apr to Jun is 2, Jul to Sep is 3 and Oct to Dec is 4
...
Converting Date to a Indian Financial Year Quarter
Assuming date is in Cell A1
...
Jan
to Mar is 4, Apr to Jun is 1, Jul to Sep is 2 and Oct to Dec is 3
...
Determine Quarter for Fiscal Year
Few countries follow different quarter other than Q1 from Jan-Mar and Q2 for Apr-Jun
...
If you want, you can concatenate "Q" in
the formula to show Q1, Q2 etc as below
="Q"&ROUNDUP(MONTH(A2)/3,0)
If your financial / fiscal year starts in Apr, then for Jan-Mar, quarter is 4 whereas for Apr to
Jun, quarter is 1 and so on
...
In this case, you can use following formula
=CEILING(MONTH(EDATE(A1,-6))/3,1)
= ROUNDUP(MONTH(EDATE(A1,-6))/3,0)
If your financial / fiscal year starts in Oct, then for Jan-Mar, quarter is 2 whereas for Oct to
Dec, quarter is 1 and so on
...
Calculate Age from Given Birthday
=DATEDIF(A1,TODAY(),"y")&" Years "&DATEDIF(A1,TODAY(),"ym")&" Months
"&DATEDIF(A1,TODAY(),"md")&" Days"
22
...
Convert from mm/dd/yy to dd/mm/yy (MDY to DMY)
Say you have following dates in MDY format
8/24/22
8/24/2022
8/04/92
08/04/1992
And you need to convert them into DMY format, then use following formula
Case1 – if your default date format is MDY
=(FILTERXML("
d/yyyy"),"/","
Case2 – if your default date format is DMY
=FILTERXML("
m/yyyy"),"/","
Note – Basically MDY to DMY and DMY to MDY are same formulas as we are just
swapping the place of MM with DD and doing nothing else
...
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
Page 9 of 60
Excel Formulas
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
25
...
Convert Time to Decimal Hours, Minutes and Seconds
Say A1 has the time 11:35 PM, then you can use following formulas
To convert into Decimal hours
=A1*24
To convert into Decimal minutes
=A1*1440
(1440 is nothing but 24*60)
To convert into Decimal seconds
=A1*86400
(86400 is nothing but 24*60*60)
(Note – You will need to format your result cell in decimal format)
27
...
58, then you can use following formula to convert it back into
time
=A1/24
Page 10 of 60
Excel Formulas
If it has decimal minutes say 1415, then you can use following formula to convert it back
into time
=A1/1440
If it has decimal seconds say 84900, then you can use following formula to convert it back
into time
=A1/86400
(Note – You will need to format your result cell in Time format)
28
...
Let's say that the date is in cell A1
...
Let's say this is Feb-2021
...
To generate in a row
=SEQUENCE(,90,A1)
=TRANSPOSE(ROW(INDIRECT(A1&":"&A1+89)))
=SEQUENCE(,DAY(EOMONTH(A1,0)),A1)
=TRANSPOSE(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))))
29
...
How to Know if a Year is a Leap Year
Page 11 of 60
Excel Formulas
Let's say that A1 contains the year
...
31
...
But, if your weekends are different (e
...
in gulf countries), you can use following formula =WORKDAY
...
First
digit is Monday and last digit is Sunday
...
You also have an option to give a range which has holidays
...
INTL(EOMONTH(A1,0)+1,-1,"0000110",D1:D10)
Where range D1:D10 contains the list of holidays
...
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
...
g
...
INTL(EOMONTH(A1,-1),1,"0000110")
Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day
...
The above example is for Gulf countries where
Friday and Saturday are weekends
...
In that case, your formula would
become
=WORKDAY(EOMONTH(A1,-1),1,D1:D10)
=WORKDAY
...
33
...
Starting
Excel 2010, you can control the weekends in the formula and function is
NETWORKDAYS
...
INTL(DATE(A1,1,1),DATE(A1,12,31),"0000110")
In the string "000110" – First digit is Monday and last digit is Sunday
...
If you have got your list of holidays in a range say B1:B20 (B1:B20 should contain dates in
date format), you can have following formulas
=NETWORKDAYS(DATE(A1,1,1),DATE(A1,12,31),B1:B20)
=NETWORKDAYS
...
Determine Number of Working Days in a Month
Let's say you need to find number of working days in year 2022 for the month of Jul where
year is in A1 and month in number format is in A2 (Hence A2 should be 7 not Jul), then you
can use following formula
=NETWORKDAYS(DATE(A1,A2,1),EOMONTH(DATE(A1,A2,1),0))
The above formula is based on the fact that Saturdays and Sundays are weekends
...
INTL
=NETWORKDAYS
...
1 defines that
particular day as weekend
...
INTL(DATE(A1,A2,1), EOMONTH(DATE(A1,A2,1),0),"0000110",B1:B20)
35
...
To find number of Mondays between these
two dates
=SUM(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=2))
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Mon"))
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=2))
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Mon"))
“Mon" can be replaced with any other day of the week as per need
...
Find Number of Friday the 13th between Two Given
Dates
Assume you have been given two dates
A1:=1-Jan-2014
A2:=25-Nov-2016
You can calculate number of Friday the 13th between these two dates by following formula
=SUMPRODUCT((WEEKDAY(SEQUENCE(A2-A1+1,,A1))=6)*(DAY(SEQUENCE(A2A1+1,,A1))=13))
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=6)*(DAY(ROW(INDIRECT(A
1&":"&A2)))=13))
37
...
If date is a regular workday, then you should show the same date
...
Hence, you should show the same date
...
In this case, formula to be used would be
=WORKDAY(A2-1,1)
Page 14 of 60
Excel Formulas
Assuming, your holidays are in E2:E3, then formula would be
=WORKDAY(A2-1,1,$E$2:$E$3)
Note – If you are using weekends other than Saturday and Sunday, use WORKDAY
...
38
...
If date is a regular workday, then you should show the same date
...
Hence, you should show the same date
...
In this case, formula to be used would be
=WORKDAY(A2+1,-1)
Assuming, your holidays are in E2:E3, then
formula would be
=WORKDAY(A2+1,-1,$E$2:$E$3)
Page 15 of 60
Excel Formulas
Note – If you are using weekends other than Saturday and Sunday, use WORKDAY
...
39
...
Then formula for finding Nth day of the year would be
=DATE(A1,1,1)+A2-1
40
...
To extract date, use following formula and format the result cell as date
= INT(A1)
To extract time, use following formula and format the result cell as time
= MOD(A1,1)
41
...
But, if your weekends are different (e
...
in gulf countries), you can use following formula =WORKDAY
...
First
digit is Monday and last digit is Sunday
...
You also have option to give a range which has holidays
...
INTL(DATE(A1-1,12,31),1,"0000110",D1:D10)
Page 16 of 60
Excel Formulas
Where range D1:D10 contains the list of holidays
...
Last Working Day of the Year
If a year is given in A1 say 2016, below formula can be used to know the last working day
of the year (format the result as date)
=WORKDAY(DATE(A1+1,1,1),-1)
=WORKDAY(DATE(A1,12,31)+1,-1)
The above formula assumes that your weekends are Saturday and Sunday
...
g
...
INTL(DATE(A1+1,1,1),-1,"0000110")
Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day
...
The above example is for Gulf countries where
Friday and Saturday are weekends
...
In that case, your formula would
become
=WORKDAY(DATE(A1+1,1,1),-1,D1:D10)
=WORKDAY
...
43
...
First what is a Julian Date?
A
...
7 Digits - YYYYDDD - 2016092 (This is 1-Apr-2016
...
What formulas to use to convert Excel Dates to Julian Dates where A1 has the date say
14-Feb-2022?
A
...
Convert from Julian Dates to Excel (Gregorian) Dates
For 7 Digits Julian Dates, following formula should be used
=DATE(LEFT(A1,4),1,RIGHT(A1,3))
For 5 Digits Julian Dates, following formula should be used
=DATE(YEAR("1-1-"&LEFT(A1,2)),1,RIGHT(A1,3))
45
...
For example, he might say that 8 should be converted to 8
Months and it should not be shown as 0 Years and 8 Months
...
Now, user can ask more
...
And for 36, he wants to see only 3 Years not 3 Years 0
Months
...
Now, the formula becomes =IF(INT(A1/12)>0,INT(A1/12)&" Years ","")&IF(MOD(A1,12)=0,"",MOD(A1,12)&"
Months")
Now an user can come and can ask for one last thing
...
Hence, 25 should be
displayed as 2 Years and 1 Month not as 2 Years and 1 Months
...
Similarly 13 should be
displayed as 1 Year and 1 Month not as 1 Years and 1 Months
...
Find the Next Week of the Day
There are 2 scenarios in this case
...
For Tuesday
to Sunday, it is not a problem as they come after 2-Jan-17 only
...
Find the Previous Week of the Day
There are 2 scenarios in this case
...
For
Tuesday to Sunday, it is not a problem as they come prior to 2-Jan-17 only
...
Count Cells Starting (or Ending) with a particular
String
1
...
Hence, it will count cells starting with both c or C
...
=COUNTIF(A1:A10,"excel*")
2
...
Hence, it will count cells starting with both c or C
...
=COUNTIF(A1:A10,"*excel")
49
...
of Cells Having Numbers Only
COUNT function counts only those cells which are having numbers
...
Reverse a String
Suppose cell A1:="qwerty" and you want to reverse it
=TEXTJOIN(,,MID(A1,LEN(A1)-SEQUENCE(LEN(A1))+1,1))
=TEXTJOIN(,,MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))
51
...
e
...
Reverse a String of Words
Suppose you have following string of words in cell A2 – "Moscow, London, Paris, Delhi,
Washington, Miami, Detroit, Berlin"
You want to reverse this string of words and want following output
...
You can use following formula for this
=TEXTJOIN(", ",,SORTBY(FILTERXML("
&"
=TEXTJOIN(", ",,INDEX(FILTERXML("
"
53
...
Get First Row Number in a Range
Suppose your range given is D15:Q99 and you want to extract the first row number which
is 15 here, then use following formula
=@ROW(D15:Q99)
Page 21 of 60
Excel Formulas
=MIN(ROW(D15:Q99))
55
...
Get First Column Number in a Range
Suppose your range given is D15:Q99 and you want to extract the first column number
which is 4 (4 means D) here, then use following formula
=@COLUMN(D15:Q99)
=MIN(COLUMN(D15:Q99))
57
...
Extract Unique Characters
Suppose, cell A1 has the value "abracadabra" and you want to extract unique characters
from this in i
...
the answer should be "abrcd"
...
Test whether a range contains only numbers
Use below formula to test whether a given range say A1:A10 contains only numbers
=ISNUMBER(--TEXTJOIN("",1,A1:A10))
60
...
Count No
...
23, it will not be counted as it is a number
...
Number of Characters in a String without considering
blanks
Say, you have a string like Vijay A
...
In this case, it has 12 including decimal and leaving blanks aside
...
Number of times a character appears in a string
Suppose you want to count the number of times, character “a" appears in a string
=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))
64
...
If your string is in A1, use following formula in A1
=COUNT(IF(ISNUMBER(--MID(A1,SEQUENCE(LEN(A1)),1))," ",0))-(A1="")
=COUNT(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))," ",0))-(A1="")
= SUMPRODUCT(--NOT(ISNUMBER((--MID(A1,SEQUENCE(LEN(A1)),1))))) -(A1="")
= SUMPRODUCT(--NOT(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))) (A1="")
Page 23 of 60
Excel Formulas
65
...
If your string is in A1, use following formula –
=COUNT(--MID(A1,SEQUENCE(LEN(A1)),1))
=COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
= SUMPRODUCT(--ISNUMBER((--MID(A1,SEQUENCE(LEN(A1)),1))))
= SUMPRODUCT(--ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))
=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))
66
...
Suppose your string is in A1, put following formula for this
...
5CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))
67
...
To generate in a row
=CHAR(SEQUENCE(,26,65))
=CHAR(SEQUENCE(,26,97))
=TRANSPOSE(CHAR(ROW(65:90)))
Page 24 of 60
Excel Formulas
=TRANSPOSE(CHAR(ROW(97:122)))
68
...
=CODE(LOWER(A1))-96
=CODE(UPPER(A1))-64
69
...
Extract nth Word from Front
Suppose you have following string – "Moscow, London, Paris, Delhi, Washington, Miami,
Detroit, Berlin"
And you want to retrieve nth word from front
...
Hence, if you wanted 2nd word, then replace that with 2
=FILTERXML("
Note – In my string, comma followed by a space is separator
...
Hence, if your separator is
simply a space, then
=FILTERXML("
71
...
Page 25 of 60
Excel Formulas
You can use following FILTERXML formula for this
Now you want to retrieve nth word from back
...
If you need 2nd last word, replace N with 2
...
72
...
Hence, if you want to
extract first 3 words
=IFERROR(LEFT(A2,FIND(REPT(" ",LEN(A2)),SUBSTITUTE(
A2&" "," ",REPT(" ",LEN(A2)),3))-1),"")
=IFERROR(REPLACE(A2,FIND(REPT(" ",LEN(A2)),SUBSTITUTE(
A2&" "," ",REPT(" ",LEN(A2)),3)),LEN(A2),""),"")
73
...
If you need to
replace last 3 words, then * * would be replaced with * * * and 2 with 3
...
Most Frequently Occurring Value in a Range
Assuming, your range is A1:A10, enter the below formula
=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))
Note – If range A1:A10 contains only numbers, then following formula is enough
=MODE(A1:A10)
75
...
SUMIF with OR Condition
For the given range, say you want to SUM the Marks
for class 1 and Subjects = English and French
...
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
...
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
...
79
...
Extract Last Name from Full Name
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
81
...
Assuming, your data is in A1, you may use following formula
=IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,1),"")
Page 28 of 60
Excel Formulas
If name is of 2 or 1 words, the result will be blank
...
82
...
Remove Middle Name in Full Name
=IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1,"
",REPT(" ",LEN(A1))),LEN(A1))),"")
=IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND("
",A1),""),"")
84
...
65, then answer would be 84 in both cases
Negative value in A1 - If A1 contains -84
...
If you want only +ve value whether value in A1 is -ve or +ve, the formula can have many
variants
...
INT(ABS(A1))
TRUNC(ABS(A1))
ABS(INT(A1))
ABS(TRUNC(A1))
84
...
65
84
84
85
84
To extract Decimal portion =MOD(ABS(A1),1)
Page 29 of 60
Excel Formulas
=ABS(A1)-INT(ABS(A1))
Positive value in A1 - If A1 contains 84
...
65
...
39, then answer would be 0
...
85
...
Get File Name through Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once
...
Get Workbook Name through Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once
...
Get Sheet Name through Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once
...
If it is not used, it will extract sheet name for the
last active sheet which may not be one which we want
...
Get Workbook's Directory from Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once
...
Perform Multi Column VLOOKUP
You know VLOOKUP, one of the most loved function of Excel
...
Now, you are having a situation where you want to do vlookup with more than 1 values
...
Below is your lookup table and you want to look up for Emp - H and Gender - F for Age
...
By concatenation, you can have as many columns as possible
...
Hence, F2&"@@@"&G2 should not have more than 255 characters
...
91
...
Hence, in the below table, I can find Date of
Birth of Naomi by giving following formula –
=VLOOKUP("Naomi",B:D,3,0)
Page 32 of 60
Excel Formulas
But, If I have to find Emp ID corresponding to Naomi, I can not do it through VLOOKUP
formula
...
Hence, you will have to use following formula –
=INDEX(A:A,MATCH("Naomi",B:B,0))
If you use XLOOKUP, the formula would simply be
=XLOOKUP("Naomi",B:B,A:A)
92
...
But in a case sensitive
VLOOKUP, answer should be 3200
...
Rank within the Groups
Suppose your have data like below table and you want to know rank of students
...
Hence, every school's rank will start with 1
...
(For ascending order, replace ">" with "<" without quote
marks)
=SUMPRODUCT((B$2:B$100=B2)*(C$2:C$100>C2))+1
Page 33 of 60
Excel Formulas
OR
=COUNTIFS(B$2:B$100,B2,C$2:C$100,">"&C2)+1
94
...
V4er7ma8", use following formula to extract
alphabets from this
=TEXTJOIN("",,IF(--(ABS(77
...
5CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13),MID(A1,ROW(INDIR
ECT("A1:A"&LEN(A1))),1),""))
95
...
V4er7ma8", use following formula to remove all
alphabets from a string
=TEXTJOIN("",,IF(--(ABS(77
...
5CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13),"",MID(A1,ROW(IND
IRECT("A1:A"&LEN(A1))),1)))
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(LOWER(A1),"a",""),"b",""),"c",""),"d",""),"e",""),"f",""),
"g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m",""),"n",""),"o",""),
"p",""),"q",""),"r",""),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""),"y",""),"z","")
96
...
Remove numbers from string
Page 34 of 60
Excel Formulas
To remove numbers from a string (for example Vij1aY A
...
If you copy this formula in a cell, it will copy this in three rows
...
Roman Representation of Numbers
Use ROMAN function
...
ROMAN works only for numbers 1 to 3999
...
Sum Bottom N Values in a Range
Suppose you have numbers in range A1:A100 and you want to sum up bottom 10 values
=SUM(SMALL($A$1:$A$100,ROW(1:10)))
=SUMPRODUCT(SMALL($A$1:$A$100,ROW(1:10)))
=SUM(AGGREGATE(15,6,$A$1:$A$100,ROW(1:10)))
=SUMPRODUCT(AGGREGATE(15,6,$A$1:$A$100,ROW(1:10)))
In case, you want to ignore 0 values (and blanks)
=SUM(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)))
=SUMPRODUCT(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)))
Both the above formulas will function only if there are at least N values as per ROW(1:N)
...
To overcome this limitation -
Page 35 of 60
Excel Formulas
Enter the below formulas
=SUM(IFERROR(SMALL($A$1:$A$100,ROW(1:10)),0))
=SUM(IFERROR(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)),0))
=SUMPRODUCT(AGGREGATE(15,6,$A$1:$A$100/($A$1:$A$100<>0),ROW(1:10)))
=SUM(AGGREGATE(15,6,$A$1:$A$100/($A$1:$A$100<>0),ROW(1:10)))
100
...
Replace 2 with N
...
If you range is B7:B50, your formula
would become
=SUM((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
=SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
101
...
=SUM((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0))
=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
=SUM((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
=SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
102
...
=SUM((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)<>0))
Page 36 of 60
Excel Formulas
=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
=SUM((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)<>0))
=SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)<>0))
103
...
Hence, for above formulas, it would work only if there are at least 10 numbers in A1 to
A100
...
We have AVERAGEIF
...
You will have to use Array formulas to
achieve these functionality
...
Calculate Geometric Mean by Ignoring 0 and
Negative Values
Geometric Mean is a useful mean and is applied only for +ve values
...
It is generally used where %ages are
involved
...
Then Geometric Mean is used to calculate not Arithmetic Mean
...
There are various ways to deal with it
and most commonly used way is to ignore <=0 values while calculating Geometric Mean
...
e
...
=GEOMEAN(IF(A1:A10>0,A1:A10))
The above formula takes into account only those values which are positive
...
106
...
, below formula can
be used
=1000+(ROW(1:20)-1)*100
=SEQUENCE(20,,1000,100)
Above formula will generate GL codes in a column
...
Abbreviate Given Names
If you have names given like Smith Johnson
Liz lotte
Christy tiara Lewisk
John
And you need to produce abbreviations or acronyms for them like below in all capitals
Smith Johnson - SJ
Liz lotte - LT
Christy tiara Lewisk - CTL
john - J
Then you can use following formula for the same for upto 3 words in the name =UPPER(TRIM(LEFT(A1,1)&MID(A1,FIND(" ",A1&"
")+1,1)&MID(A1,FIND("*",SUBSTITUTE(A1&" "," ","*",2))+1,1)))
Explanation for the formula
1
...
MID(A1,FIND(" ",A1&" ")+1,1) FIND(" ",A1&" ") - Find finds the first space in the given name to locate the start of the
middle name
...
+1 has been added to start the MID
position from where the middle name starts
...
MID(A1,FIND("*",SUBSTITUTE(A1&" "," ","*",2))+1,1))
SUBSTITUTE(A1&" "," ","*",2) will replace the second blank with a *, hence we can find the
position of * to locate the start of last name
...
+1 has been added to start the MID
position from where the last name starts
...
TRIM will remove all blanks inserted because of 2 or 3
...
UPPER will convert the string to all capitals
...
In this case, the formula would become =UPPER(TRIM(LEFT(A1,1)&IFERROR(MID(A1,FIND("
",A1)+1,1),"")&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1,1),"")))
Note - This technique can be used to extend up to many words
...
Hence for upto 6 words, the formula would become
=UPPER(TRIM(LEFT(A1,1)&IFERROR(MID(A1,FIND("
",A1)+1,1),"")&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1,1),"")
&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1,"
","*",3))+1,1),"")&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",4))+1,1),"")
&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",5))+1,1),"")))
108
...
Hence, if A1=1, you want "A"
Hence, if A1 =26, you want "Z"
Hence, if A1=27, you want "AA" and so on
...
Get Column Range for a Column Number
Let's suppose, you have a number in A1 and you want to get the column range for that
...
The formula to derive the column range would be be =SUBSTITUTE(ADDRESS(1,A1,4)&":"&ADDRESS(1,A1,4),1,"")
Page 40 of 60
Excel Formulas
110
...
Hence, if you have a
series like below -
And you give =LARGE(A1:A10,3), you get the answer as 18
Now, if we have a series like below
Now, you give =LARGE(A1:A10,3) and now the result is 24
...
Hence, LARGE function will sort the above array
as {24,24,24,22,22,18,18,9} and 3rd largest is 24
...
The formula for such case would be
=LARGE(UNIQUE(A1:A10),3)
=LARGE(IF(FREQUENCY($A$1:$A$10,$A$1:$A$10)<>0,$A$1:$A$10),3)
111
...
And it is very easy to do - just say =COUNTIF("A1:A100",">5") and
it finds all the values within the range A1 to A100 which are greater than 5
...
Try putting in following
formula =COUNTIF((A3, A8),">5") and it will give you #VALUE error
...
Now, you will have
to use a formula like =(A3>5)+(A4>5)+(A5>5)+(A8>5)+(A24>5)+(A40>5)+(A45>5)+(A89>5)
The formula becomes cumbersome as the number of cells increase
...
This single formula can take care of contiguous (like A3:A5) and noncontiguous ranges both =SUM(COUNTIF(INDIRECT({"A3:A5","A8","A24","A40","A45","A89"}),">5"))
112
...
Formula for calculating number of words in a cell =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")
Formula for calculating number of words in a range =SUMPRODUCT(LEN(TRIM(A1:A100))-LEN(SUBSTITUTE(TRIM(A1:A100),"
",""))+(TRIM(A1:A100)<>""))
113
...
For example,
74 = 7 + 4 = 11 = 1 + 1 = 2
23 = 2 + 3 = 5
Page 42 of 60
Excel Formulas
78 = 7 + 8 = 15 = 1 + 5 = 6
1234567 = 1 + 2 + 3 + 4 + 5 + 6 + 7 = 28 = 2+ 8 = 10 = 1+ 0 = 1
The formula to achieve the same is
=MOD(A1-1,9)+1
114
...
For example 1,2,3,4,1,2,3,4,1,2,3,4
You can use the below formula and drag down =MOD(ROWS($1:1)-1,4)+1
Replace 4 with with any other number to generate any other sequence
...
Repeat a Number and Increment and Repeat
...
For example 1,1,1,1,2,2,2,2,3,3,3,3
...
Generate Non Repeating Random Numbers
through Formula
Suppose, you want to generate non-repeating random numbers between 1 to 30, you can
use following formula in A2 and drag down
=INDEX(UNIQUE(RANDARRAY(30-1+1,,1, 30, TRUE)), SEQUENCE(10))
In above formula, SEQUENCE(10) means that we want to generate 10 random numbers
...
Suppose, you had put the formula in G4, this should be replaced with $G$3:$G3
...
For example, Excel 2007 will support IFERROR whereas 2003
supports ISERROR) =LARGE(INDEX(ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),,),
RANDBETWEEN(1,30-ROW(A1)+1))
117
...
a
...
com and you need to retrieve
v
...
verma which is user name in the mail ID
...
Extract Domain Name from an E Mail ID
If you want to retrieve domain name which in above example is gmail
...
Location of First Number in a String
Suppose you have A1: = “abfg8ty#%473hj" and you want to find what is the position of first
number in this
...
You can use following
formula –
=IFERROR(AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A1,SEQUENCE(LEN(A1))),1),"")
=IFERROR(AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A1,ROW(INDIRECT("1:"&LEN(A1)
))),1),"")
120
...
You can use following formula
to find this –
=IFERROR(AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A1,SEQUENCE(LEN(A1))),1),"")
=IFERROR(AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A1,ROW(INDIRECT("1:"&LEN(A1)
))),1),"")
121
...
Find First Numeric Value in a Range
=INDEX(FILTER(A1:A100,ISNUMBER(A1:A100),""),1)
=IFERROR(INDEX(A1:A100,MATCH(1,--ISNUMBER(A1:A100),0)),"")
123
...
Find First non Numeric Value in a Range
Page 45 of 60
Excel Formulas
=INDEX(FILTER(A1:A100,ISTEXT(A1:A100),""),1)
=IFERROR(INDEX(A1:A100,MATCH(1,--ISTEXT(A1:A100),0)),"")
125
...
Find Last Used Value in a Range
= IFERROR(LOOKUP(2,1/(A1:A100<>""),A1:A100),"")
127
...
This should take care of if future entries are done
...
–
Use this formula –
=SUM(INDEX(SORTBY(B2:B20,A2:A20,-1),SEQUENCE(12)))
=IFERROR(SUM(OFFSET(INDIRECT("$B"&COUNTA($A:$A)),0,0,IF(COUNTA($A:$A)12>0,-12,-COUNTA($A:$A)+1),1)),"")
Page 46 of 60
Excel Formulas
Note – If you have only one column say B, replace $A with $B
...
Generate a Unique List out of Duplicate Entries
Suppose, you have entries in A2:A100 and you want to generate a list containing only
unique entries in column C starting C2
...
=IFERROR(INDEX($A$2:$A$100, MATCH(0, IF($A$2:$A$100<>"",COUNTIF(C1:$C$1,
$A$2:$A$100)), 0)),"")
OR
=IFERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($C$1:$C1,$A$2:$A$100&""),0)),"")
129
...
It is a fairly easy job to do You will need to use PMT function for this
...
of payments
...
i
...
Generally,
interest rate is specified yearly i
...
10
...
This you should divide by 12 to arrive
at monthly rate
...
If it is quarterly, rate = 10
...
625% and nper would be 3x4 = 12
If it is annual, rate = 10
...
You will need to put negative value of this in your formula
...
+ve / -ve PMT requires some explanation though you may choose to ignore
...
If you are taking a loan, hence cash in, hence pv is +ve
...
Hence, PMT is -ve
...
Hence pv is -ve
...
Hence, PMT is +ve
...
I recommend you should not worry about
this
...
Generally, it is 0 as any lender
will like to recover its money fill
...
The formula used in the below picture is =PMT(B1/12,B2,-B3,B4,B5)
Bonus Tip = If you use ABS function, then there would be no need to put negative value of
PV
...
EMI
Financial Function - Calculate Interest Part of an
Now the EMI for a month = Interest for that month and Principal for that month
...
Excel defines IPMT as "Returns the interest payment for a given period for an investment
based on periodic, constant payments and a constant interest rate"
The syntax of IPMT is IPMT(rate, per, nper, pv, [fv], [type])
...
of payments
...
i
...
Generally,
interest rate is specified yearly i
...
10
...
This you should divide by 12 to arrive
at monthly rate
...
If it is quarterly, rate = 10
...
625% and nper would be 3x4 = 12
If it is annual, rate = 10
...
You will need to put negative value of this in your formula
...
+ve / -ve IPMT requires some explanation though you may choose to ignore
...
If you are taking a loan, hence cash in, hence pv is +ve
...
Hence, IPMT is -ve
...
Hence pv is -ve
...
Hence, IPMT is +ve
...
I recommend you should not worry about
this
...
Generally, it is 0 as any lender
will like to recover its money fill
...
The formula used in the below picture is =IPMT(B1/12,B2,B3,-B4,B5,B6)
Also, since Interest will vary every month, hence it makes sense to calculate it for each
month
...
Bonus Tip = If you use ABS function, then there would be no need to put negative value of
PV
...
EMI
Financial Function - Calculate Principal Part of an
Now the EMI for a month = Interest for that month and Principal for that month
...
To calculate the principal part of an EMI,
you will need to use PPMT
...
"
The syntax of PPMT is PPMT(rate, per, nper, pv, [fv], [type])
rate: You rate of interest
per: Period for which you want to calculate Principal
nper: No
...
Your nper and rate should be on the same scale
...
e if you are
planning to pay up monthly, the rate in your formula should be monthly only
...
e
...
5% per year
...
Hence, if you wanted 3 years loan, it means nper would 3x12=36 months
...
5%/4 = 2
...
5% and nper = 3
pv: Your loan amount
...
If you
don't put negative value, your EMI would be in negative but answer would be same though
with negative sign
...
It depends
upon your cashflow
...
But every month,
you will have to pay up something, hence cash out
...
If you are investing,
hence cash out
...
But every month, you will be receiving something, hence
cash in
...
Page 50 of 60
Excel Formulas
Now what is +ve or -ve is simply your preference
...
fv: Your remaining value after you finish your installment
...
(Default is 0)
type: 0 - At the end of the period, 1 - At the beginning of the period (Default is 0)
Also note, fv and type are optional and may not be required in your formula
...
Columns H & I carry Principal for each month
...
Hence, formula in this case would be =ABS(PPMT(B1/12,B2,B3,B4,B5,B6))
132
...
So, you want to know how many
months will be taken to pay off a loan completely
...
You will need to use NPER function for this
...
"
Page 51 of 60
Excel Formulas
Syntax of NPER is NPER(rate,pmt,pv,[fv],[type])
...
You will need to put -ve value of this in your formula
...
i
...
Generally, interest rate is specified yearly
i
...
10
...
This you should divide by 12 to arrive at monthly rate
...
If it is quarterly, rate = 10
...
625% and nper would be 3x4 = 12
If it is annual, rate = 10
...
You will need to put +ve value of this in your formula
...
Both can't be +ve and -ve at the same time
...
It depends upon your
cashflow
...
But every month, you will
have to pay up something, hence cash out
...
If you are investing, hence
cash out
...
But every month, you will be receiving something, hence cash in
...
fv: Your remaining value after you finish your installment
...
(Default is 0)
type: 0 - At the end of the period, 1 - At the beginning of the period (Default is 0)
Also note, fv and type are optional and may not be required in your formula
...
Financial Function - Calculate Interest Rate
You want to take a loan
...
Excel makes it easy to do
...
Page 52 of 60
Excel Formulas
Excel defines RATE as "Returns the interest rate per period of an annuity
...
If the successive results of RATE do not
converge to within 0
...
"
Syntax of RATE is RATE(nper, pmt, pv, [fv], [type], [guess])
...
Typically in months
...
You will need to put -ve value of this in your formula
...
i
...
pv: Your loan amount
...
Note - Either PMT or PV should be -ve
...
+ve / -ve requires some explanation and this can not be ignored
...
If you are taking a loan, hence cash in, hence pv is +ve
...
Hence, PMT is -ve
...
Hence pv is -ve
...
Hence, PMT is +ve
...
Generally, it is 0 as any lender
will like to recover its money fill
...
If RATE does not converge, try
different values for guess
...
Once again,
note that if PMT is monthly, then Guess should also be monthly
...
Also note, fv, type and guess are optional and may not be required in your formula
...
Financial Function – Calculate Compounded
Interest
As part of our Mathematics courses in our childhood, we had learned about Compounded
Interest
...
The formula to be used is FV
...
The syntax of FV is FV(rate,nper,pmt,[pv],[type])
You require only 3 pieces of information for Compounded Balance
...
Now rate and nper
should be on the same scale
...
If
interest rate is quarterly, then nper should be in quarter
...
pv: This is the initial principal and it has to be specified in -ve
...
)
The formula used in below picture for Monthly
=FV(B1/12,B3*12,0,-B2)
The formula used in below picture for Quarterly
=FV(F1/4,F3*4,0,-F2)
The formula used in below picture for Yearly
=FV(J1,J3,0,-J2)
Page 54 of 60
Excel Formulas
135
...
The interest rate which is
quoted is called "Nominal Interest Rate"
...
Hence, if they quote 12% interest for a loan, this is yearly figure
...
They simply say that you need to pay 1% monthly interest which
has been derived by annual interest rate / 12 which 12%/12=1% in this case
...
But lending financial
institutions doesn't quote this higher rate as it will make your loan cost look higher
...
Excel describes EFFECT - Returns the effective annual interest rate, given the nominal
annual interest rate and the number of compounding periods per year
...
Nominal Rate - Annual Interest Rate
npery - Compounding periods in a Year
...
For quarterly
payments, it is 4
...
68% for a monthly payment
...
The formula used is =EFFECT(B1,B2)
Now, if you are making an investment and making monthly payments, you will be getting
annual return of 12
...
Page 55 of 60
Excel Formulas
136
...
The formula to calculate CAGR is very simple
...
We need to know only PV, FV and NPER
...
So, to calculate CAGR, we will use following formula –
=RATE(5,,-10000,16448)
=RATE(5,,10000,-16448)
Hence, in the above formulas, you need to put either PV as negative or FV as negative
...
If you don't have FV but a series of fixed payments (say investment of 3000 every year),
you can use –
=RATE(5,-3000,10000)
=RATE(5,1000,-10000)
Now, let's come back to AAGR
...
If your data set is laid
like below, you can use following formula to calculate AAGR –
=SUMPRODUCT((B3:B11-B2:B10)/(B2:B10))/(ROWS(B2:B10))
Page 56 of 60
Excel Formulas
137
...
If you see your electricity bills, you will notice
following type of entries (values are for illustration purposes only, please do not attach
any meaning to them)
0 – 50 Units – $1
...
0 per Unit
101-500 Units – $3
...
5 per unit, next 50 will be charged at
$2
...
5 per unit, next 1500 units will
charged at $6 per unit and any unit after 2000 will be charged at $ 9 per unit
...
5 = $55
...
5+(87-50)*2 = $149
Hence, if your bill is 415 units, your charges will be = 50*1
...
5 =
$1277
...
5+50*2+400*3
...
5+50*2+400*3
...
Consider the below slabs
...
Hence, if your income is $40000, your income tax will be =(40000-10000)*10% = $3000
Hence, if your income is $90000, your income tax will be =40000*10%+(9000050000)*20% = $12000
Hence, if your income is $210000, your income tax will be
=40000*10%+50000*20%+(210000-100001)*30% = $47000
Hence, if your income is $850000, your income tax will be
=40000*10%+50000*20%+400000*30%+(850000-500000)*35% = $256500
Now, how to calculate this in Excel through a single formula
...
The only per-requisite is that you need to set up your data
appropriately for this purpose as given below
...
LTRIM and RTRIM through Excel Formulas
So, we have TRIM function in Excel
...
The same is
provided in VBA but most of the Excel users are not using VBA
...
Page 58 of 60
Excel Formulas
Before I delve into LTRIM and RTRIM, let's recap TRIM
...
So, it removes all leading
spaces, all trailing spaces and all spaces between words except one
...
Below are the examples of operating with TRIM function –
"Vijay A Verma" = "Vijay A Verma" (two spaces between Vijay and A have been reduced
to one
...
Now, I talked about that most programming languages including provide LTRIM and
RTRIM function
...
LTRIM removes all leading spaces and leaves all other spaces untouched
...
It has left spaces between two words and spaces after last
word untouched)
2
...
Hence, if I
operate LTRIM on following –
" Little Mary " = " Little Mary" (All three trailing spaces have been trimmed so that
is no space after last word
...
The formula for RTRIM
Page 59 of 60
Excel Formulas
=IFERROR(REPLACE(A1,MAX(INDEX((MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1)
<>" ")*ROW(A1:INDEX(A:A,LEN(A1))),,))+1,LEN(A1),""),"")
Above function would act like RTRIM that is it would remove all trailing spaces and would
leave all other spaces untouched
Title: Microsoft 365 Excel formulas
Description: Microsoft 365 Excel formulas are powerful tools that help users efficiently analyze and manipulate data. With a wide range of functions—such as SUM, VLOOKUP, IF, and INDEX—Excel formulas automate complex calculations, streamline workflows, and provide valuable insights from large datasets. Whether you're managing budgets, tracking performance, or generating reports, Excel formulas are essential for boosting productivity and making data-driven decisions with ease.
Description: Microsoft 365 Excel formulas are powerful tools that help users efficiently analyze and manipulate data. With a wide range of functions—such as SUM, VLOOKUP, IF, and INDEX—Excel formulas automate complex calculations, streamline workflows, and provide valuable insights from large datasets. Whether you're managing budgets, tracking performance, or generating reports, Excel formulas are essential for boosting productivity and making data-driven decisions with ease.