Tuesday, July 28, 2020

A quick tutorial on the percentage (%) operation in Excel

In this tutorial, we will focus on a very simple and tiny operation of MS-Excel. With a single formula, this operation can be done. Yes, we are talking about ‘Percentage’ or ‘%’ operation of Excel here.

As we already told that this content is a tiny one, so without further more delay, let’s roll to the topic, In MS-Excel how can you perform percentage calculation.

By understanding of general mathematics anyone can calculate a percentage of a number in Excel, just need to multiply the percentage value by the number that you want the percentage of.

Suppose, you want to calculate 15% of 600, so just multiply 15% by 600. So, the result will become 90 for the above case.
Basically, % operator is used to denoting percentage and it means the preceding number for our case 15 should be divided by 100. And, 15 divided by 100 turns 0.15, then just multiply the resultant with the number you want to calculate.
0.15*600 = 90

In Excel, you can simply write down the formula in a cell to check the outcome.


Note: Always, start with a ‘=’ before any function or formula in Excel.

Now, let’s consider a situation, you want to buy a specific product from a shop and currently, the shop has a discount offer of 15% for the product. Also, let the original price of that product was $1280. So, how can you calculate the current price of that product after the discount? 

Please, take a closer look at the below screenshots and hopefully, you will get your answer. To earn more proficiency with the percentage in Excel, we suggest you, perform the calculation with many more data and you will strongly cope with the percentage formula in Excel. 

In the near future, we will cover Sales Tax Calculation where percentage formula will be discussed again and you will also learn the basics of invoice bills and calculations.

That’s all for today. Thanks for staying with tutorialabc.comIf you have slight confusion about anything or have any queries, please feel free to knock us via our contact page.

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.

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.