Sunday, June 28, 2020

How simply Age can be calculated in Excel


Well, sometimes it is necessary to extract the correct age of a person from a dataset in an excel file and with a simple trick or can say, by using a formula in Excel, you can calculate the age of any person based on the data provided to you.

The math behind the age calculation or how old you are based on some criteria. Basically, it requires the Date of Birth (DOB) and the current date. And, the current date can be obtained by using Today () function which is a built-in function of Excel. Then just need to find the difference from the result of Today () to DOB and divide the entire value by 365 as you know 365 is the equivalent of a year.

One thing to remember here, the number format of the excel sheet has to be in General format to get the desired result. Please check the number format of your excel sheet before proceeding.

General formula:

=(Today () - DOB)/365


Though by using this general formula you will get the correct age of a person but it will provide you some fractional value after the year. To remove the fractional part you need to use ‘int’ before the general formula. Like,

      =int(Today () - DOB)/365

Now, you will get the exact year of that person based on the Date of birth till today.

Here, we are adding some screenshots with very few data and steps to perform the above-mentioned calculation. Please look carefully and follow along with those steps. Hopefully, you’ll understand it clearly.








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





Wednesday, June 17, 2020

RANK.AVG Function in 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.




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.comIf you have any queries, please feel free to knock us via our contact page.

Friday, June 5, 2020

Working With Rank Function in Excel

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.