Data analysis: Function

Showing posts with label Function. Show all posts

Tuesday, September 8, 2020

Usage Of LOWER Function In Excel

LOWER function in Excel works in a similar manner of UPPER function but it will do the opposite process of UPPER function. In brief, The LOWER function returns a lower case format of a given text. If you are unsure about the process of UPPER function then please check below URL first, which will give you a clear concept about the working principle of these sort of functions.

URL: https://www.tutorialabc.com/2020/08/upper-function-in-excel.html

Basic Syntax

=LOWER(text)

It passes a single argument, “text” – which text will be converted to lower case.

Example

Suppose, you have a wide range of text in a spreadsheet which are in upper case or in some different format which give you a little bit disturbance to resolve your project with Excel. In that case, you may try to use the LOWER function to sort out the problem.

But, keep in mind that - Spaces, numbers and punctuation will be unaffected in LOWER function.

For the demonstration, we use some upper case text including space, number and punctuation markup and finally, we will get the lower case version of those text using LOWER function.

So, please take a closer look on the below screenshots and follow along with the sequence. Hopefully, it will help you to understand the LOWER function in excel properly.

(Working Data)

(LOWER function Syntax)

Soon, we will provide a similar type of tutorial known as PROPER function in Excel and till then keep learning and stay with tutorialabc.com! For any query or suggestions, feel free to contact.

Usage of UPPER function in Excel

MZA Shawn Basic , Data analysis

UPPER function in excel converts a given text into an upper case version. But, it will not affect on any numbers or punctuation marks at all. This function will help you to sort out a spreadsheet where most of the text entries are in lowercase. By using, UPPER function you can simply and very quickly resolve the issue.

Basic Syntax

=UPPER(text)

It passes a single argument, “text” – which text will be converted to upper case.

Example

Suppose, you have a wide range of text in a spreadsheet which are in lower case format and you want to convert those texts into upper case. Also, have a number value and punctuation and you want to use UPPER function to look at the differences.

“john”, “doe”, “9871”, “Hi!” are some values in a your spreadsheet. Now, follow the below image sequences and try it out by yourself. Hopefully, it will help you to understand the UPPER function in excel properly.

(Sample Data)

(Writing UPPER function)

(Selecting text from the cell)

(After converted to upper case)

(Ignoring numbers & punctuation)

Next, we will provide a tutorial on LOWER function in Excel and till then keep learning and stay with tutorialabc.com! For any query, feel free to contact us.

Working with ARABIC Function in MS-Excel

MZA Shawn ARABIC , Basic

The Arabic function converts a Roman numeral into an Arabic numeral in Excel Spreadsheet. Basically this function works just the opposite of the ROMAN function of Excel. Check the ROMAN function tutorial here,

https://www.tutorialabc.com/2020/08/working-with-roman-function-in-ms-excel.html

In simple words, Arabic function can convert Roman numerals into standard text format.

Syntax

=ARABIC(text)

Here, the text argument is a text representation of a Roman numeral and it supports maximum 255 characters.

Keep in mind,

If the text argument passed directly to the function, use Double-Quotes, else you will get #NAME? Error.

Example

=ARABIC(“XI”) , correct

=ARABIC(XI), incorrect and produce #NAME? Error.

If an empty text string is passes as an argument, the Arabic function returns the value 0. Excel’s earlier version before 2013, Arabic function wasn’t available.

Here, we are adding a GIF file which converts some ROMAN numerals into regular numbers by using the ARABIC function. Please take a close look at the GIF and reload the GIF or Press Ctrl+R on your browser to watch the GIF repeatedly.

That’s set! ARABIC function is a very tiny piece of built-in function in Excel, but with the help of this function you may save your precious time. So, if anything remains unclear to you or have any query, feel free to contact us. Thank you and stay with tutorialabc.com for more interesting Excel-based tutorials.

Working with ROMAN Function in MS-Excel

MZA Shawn Basic , Excel

MS Excel has a very useful function called ROMAN function which is basically used for converting a regular number into a ROMAN numeral representation. In simple words, suppose you put a number in a cell of Excel and ROMAN function will convert the exact equivalent ROMAN numerals as a text of that number.

Syntax

=ROMAN(number,[form])

Example

Suppose, you put the number ‘9’ in a cell and for converting the number ‘9’ into ROMAN numerals you need to write the function ‘=ROMAN(9)’ and it will return ‘IX’ for you.

As you can see from the above function, there are only two arguments that passes through the function.

number: A regular number also considered as Arabic numeral in Excel which you would like to convert.

form: This field is optional and it provides the type you want to see in the ROMAN numeral. It consists 0-4 means Classic – Simplified. Check it for yourself!

Keep in mind, ROMAN function can be functional only for positive numbers and less than 4000 which means 3999 is the last number for ROMAN function to be operated.

Here, we are adding a GIF file which converts some regular numbers into ROMAN numerals by using a ROMAN function. Please take a close look at the GIF and reload the GIF or Press Ctrl+R on your browser to watch the GIF repeatedly.

(Roman function GIF, Open this image in a new tab)

Hopefully, the above GIF will give you a clear understanding of how actually ROMAN function in MS Excel works. If you have any doubts or want to learn anything new in Excel, please let us know via our contact page. We will respond back ASAP! Thank you and stay with tutorialabc.com for more Excel-based tutorials.

RANK.AVG Function in Excel

MZA Shawn Basic , Excel

RANK.AVG works in the same manner as a simple Rank function. It returns the rank of defined values of a given dataset, but if duplicate or same value lies within the dataset then the RANK.AVG function will assign an average rank to each set of duplicates. Keep in mind, RANK.AVG function only consider numeric values and ignore other values (texts, date and so on)

If you are not familiar with the Rank function of Excel till now, then please follow this tutorial first for getting the concept.

URL: https://www.tutorialabc.com/2020/06/working-with-rank-function-in-excel.html

Basic Syntax

= RANK(number, ref, [order])

Where, ‘number’ is the value whose rank will be determined. Then ‘ref’ is the array or range containing the dataset to consider and finally, ‘order’ is the last argument but it is optional. This argument will help you to maintain the order of the list and rank them according to descending or ascending order.

0 or leave the argument empty: It will rank the list in default descending order.
1: for ascending order please type 1 in the last argument field of the rank function.

Example

Suppose, you have a worksheet which contains data like the below screenshot and you can see that, this worksheet contains a duplicate value.

(Sample data)

So, in this sort of case, we can rank those values by using Rank.Avg function. Here, we are adding a GIF file which includes all the steps required to complete the full process. Please take a good look at the GIF and hopefully, you will get a clear understanding of Rank.Avg function.

(Complete Process in a GIF)

Thanks for staying with tutorialabc.com! If you have any queries, please feel free to knock us via our contact page.

Working With Rank Function in Excel

MZA Shawn Basic , Data analysis

Rank function is a very useful function in case of ranking numeric values within a list. It will return rank of a number against a range of numbers and will ignore other sorts of values including text, character etc.

In this short tutorial, we’ll learn how you can use rank function and what sort of result you will get by using this function. Hopefully, this beginner-level tutorial will give you a clear understanding of rank function and you will be able to work with it independently.

Let’s start,

Basic Syntax

= RANK(number, ref, [order])

As you can see from the syntax, Rank function passes three arguments which are number, ref & order.

number: number is the first argument of rank function and it means the number we would like to check within a list of numbers.
ref: this argument is used to compare within the list of numbers. We could select the entire cell by referencing the cell or we could also select some values within the cell manually.
order: this argument is optional but it’ll help you to maintain the order of the list and rank them according to descending or ascending order.

- 0 or leave the argument empty: It will rank the list in default descending order.

- 1 : for ascending order please type 1 in the last argument field of the rank function.

Example

Suppose, you have a worksheet which contains data like the below screenshot and from those data you want to rank them based on Total Profit.

So, now at first, choose the blank cell E2 and start typing “=Rank” and click on the suggested Rank function, like in the below image,

Then just pass the arguments according to the format of the function and every argument will be separated by a comma.

For our case, we just select the D2 cell as the first argument of the rank function, then select all other cells of the Total Profit header which contains numeric values as reference (the second argument) and at last, choose descending order so we select ‘0’ as rank functions final argument.

And, we get the rank for that number within the list.

Finally, just drag your mouse to the last value of the worksheet

and you will get the ranks of those numeric values within the list. Like the below screenshot,

Finally, we are adding a GIF image for you so that you can cope with the total process of the Rank function.

Isn’t it simple! Just try to choose the other ways of passing the arguments on your own and see the difference. It will help you to understand the Rank function more precisely. Thanks for staying with tutorialabc.com!

If you have any queries, please let us know through our contact page.

Usage of SUMIF & SUMIFS in Excel

MZA Shawn Basic , Data analysis

Today we will cover two basic yet very essential functions of MS Excel.

SUMIF &
SUMIFS

We believe, you already know the functionality of sum function. If you don’t know, please check out this SUM function tutorial.

SUMIF and SUMIFS have the same functionality likewise a SUM function but these two functions contain additional criteria to lookup the entire data table and based on those criteria it performs the sum operation.

Keep in mind, SUMIF function accepts only one criteria whereas SUMIFS function takes multiple criteria.

Introduction of SUMIF

SUMIF comes up with a comparison operator like greater than, less than or equal, etc, It supports both character and integer.

When there are a large number of data set their manual calculation is ineffective when you have to find out the range of sum based on condition.

General syntax

=SUMIF (range, criteria, [sum_range])

Example & Working way,

Just think of a biscuit company inventory, where you need to find out how many kgs of flour ingredients is available there. So you must need a condition to apply in the sum that only flour ingredients need to sum up. Therefore, SUMIF function you can infuse a condition on SUM.

Now, for clear understanding, follow the below steps carefully.

1. Try to create or just copy & paste the below dataset on your excel worksheet as an example.

Product	Region	salesRep	Customer	Sales
bellen	South	zubbar	e	123
sun	East	zubbar	a	234
bellen	west	zubbar	g	345
sun	north	zubbar	w	456
zasa	East	zubbar	e	567
bellen	west	zubbar	e	678
zisa	north	zubbar	a	789
bellen	west	zubbar	g	344
sun	north	zubbar	w	1222
mss	East	akib	e	2100
zasa	west	akib	e	2978
bellen	East	akib	a	3856
sun	west	akib	g	4734

2. Then choose a blank cell and type “=SUMIF (A2:A14,"bellen", E1:E14)” to calculate the sum of bellen product sales and you will get a result of 4568 Sales based on the criteria.

Then, try to work with other products from the dataset to calculate the sum of the sales of those products. Hopefully, it will give you a clear concept, how actually SUMIF function work in MS-Excel.

Introduction of SUMIFS

SUMIFS function is almost the same as SUMIF. It has the power where you can set multiple conditions or on excel sense criteria.

General syntax

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

Example & Working way,

1. Again, copy & paste the given dataset on your excel worksheet or prepare one by yourself.

Product	Region	salesRep	Customer	Sales
bellen	South	zubbar	e	123
sun	East	zubbar	a	234
bellen	west	zubbar	g	345
sun	north	zubbar	w	456
zasa	East	zubbar	e	567
bellen	west	zubbar	e	678
zisa	north	zubbar	a	789
bellen	west	zubbar	g	344
sun	north	zubbar	w	1222
mss	East	akib	e	2100
zasa	west	akib	e	2978
bellen	East	akib	a	3856
sun	west	akib	g	4734

2. Choose a blank cell where you want to show your sum result and start typing “=SUMIFS(E2:E14,A2:A14,"bellen",C2:C14,"zubbar")” to calculate the total sales of the product bellen which is sold under a salesRep named zubbar. You will get a result of 1490 Sales based on those criteria.

Hopefully, this tutorial makes some sense and got a clear understanding, how actually SUMIF & SUMIFS function work and also know the difference between them.

That’s set for today. If you have any inquiries or suggestions regarding this please write to us in the comment section. Stay with tutorialabc for more easy excel tutorials.

Tuesday, September 8, 2020

Usage Of LOWER Function In Excel

Friday, August 28, 2020

Usage of UPPER function in Excel

Tuesday, August 25, 2020

Working with ARABIC Function in MS-Excel

Tuesday, August 11, 2020

Working with ROMAN Function in MS-Excel

Wednesday, June 17, 2020

RANK.AVG Function in Excel

Friday, June 5, 2020

Working With Rank Function in Excel

Thursday, April 23, 2020

Usage of SUMIF & SUMIFS in Excel

Follow Us

Blog Archive

Popular Posts

Recent Comments