Filtering Bold Data

Dear readers today I came to know about a situation where the user was looking to filter data in a range, but he wanted to filter only those cells in range which has BOLD formatting.

I thought of doing it using VBA as there was no option that came to my mind that time.  The below code worked for me.

Sub FilterBold()

Dim myRange As Range

Set myRange = Application.InputBox(Prompt:="Please Select a Range", Title:="InputBox Method", Type:=8)
myRange.Select
Application.ScreenUpdating = False
For Each myRange In Selection
If myRange.Font.Bold = False Then
myRange.EntireRow.Hidden = True
End If
Next myRange
Application.ScreenUpdating = True
End Sub

With this post I am entering into the ocean of coding and will try to match pace with my other excel blogger friends.

"HAPPY LEARNING"

All about your LOAN in Excel

Are you planning to borrow money?  If yes, give my EMI Calculator a look and you'll know a lot about it.

It helps you to know how much EMI you'll be paying, during repayment period how much amount you'll have to pay extra and other things.

I have used PMT function to get the value of EMI.  The syntax goes like this:

PMT(rate,nper,pv,fv,type)

Rate is the interest rate for the loan.

Nper is the total of payments for the loan.

Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments are due.

Instead of going into the details of this function, I would suggest you to download the file and make use of it. Click to Download the file from here.

"HAPPY LEARNING"

Create Dynamic Range for your Charts

Hi guys recently I got an email where the sender has requested to create chart in such a manner that graph adjusts itself to the changing figures in the table or data. I provide him a solution which look something like the below image.

Download the file from here: DYNAMIC RANGE

Now the real question here comes is, how this was done? For that I have used Name Manager from Formulas tab.  Don't worry I will tell you step by step process to create this chart and dynamic data input range for it.

Suppose I have a table like given in the image below and I want to create a chart for this table in such way that if I enter Budget and Actual sales in column C & D, these values will automatically get reflected in my chart.

Now first I will name my ranges to be plotted in charts. For that click Formulas > Name Manager > New

For Budget column name it as Budget_data or whatever you like and in refers to box at the bottom write the formula as =OFFSET(Sheet1!$C$5,0,0,COUNT(Sheet1!$C$5:$C$16)). Similarly for 2011 data name it as Actual_data and refers to =OFFSET(Sheet1!$D$5,0,0,COUNT(Sheet1!$D$5:$D$16))  have a look at the image below.

Before moving ahead I want to explain what the above formula is doing.  To put it in layman language, OFFSET is helping us to select the range starting from C5 and COUNT function is helping the formula to decide the height of range by counting the number of data entered in the table.

Once we're done naming ranges now we'll create charts and do edits required to make it dynamic.

Select your data to be plotted, here select B4:D15 and click Insert > Charts > Column > Clustered Column.  It will give you a chart appearing as in 1 of image below.

Now right click in your chart and select change series chart type as in 2 of the image.  Select Line chart type.

You can also change the layout of your chart and tittle as done in 3 of the image.

Now in 4 comes the most important part of this trick.  Here we will adjust the data source for our chart and define the two series shown in the chart.

As shown in 4 of the above image, when we click Select Data it will open Select Data Source Box where under Legend Entries (Series) we'll edit Series1 & Series2.  In the following image below we have made edits to Series1 by referring Series name to cell C3 & Series values to "Budget_Data" which we named above.  Similarly for Series2 we referred Series name to cell D3 & Series values to "Actual_Data".

What this will do is make our chart responsive to the data we enter in table range B4:D15.  When we enter figures for any month it will automatically plot the same in the corresponding chart.  And that exactly was our goal as well.

Hope you learned something out of this trick.  "HAPPY LEARNING"

Conditional Lookup using Multi Criteria

Hello readers today's post will be short but I assure you it will help you tackle problem which you may be facing or might face in future. Suppose you want to look up any value based on two criteria, for example in the below image you want to know the price in F8 based on Make & Color in F2 & F5 respectively.

I know the solution to this can be found using filter feature, but being big fan of functions I would like to provide you formula to find it as well.  You'll use a combination of INDEX & MATCH function to get your figure here. The formula in F8 will be as follows:

F8: {=INDEX(C2:C10,MATCH(F2&F5,A2:A10&B2:B10,0))}

This is an array formula and to make it works you'll have to press (Ctrl+Shift+Enter) CSE.

Download sample file: MULTI-CRITERIA LOOKUP

Are you above or below AVERAGE in Excel?

AVERAGE this word is no new to any of us and it's meaning is known to all of us.  But what does it exactly means? The literal meaning is "a quantity, rating, or the like that represents or approximates an arithmetic mean".  For statistician it has always meant Arithmetic Mean and when used as an adjective it means typical; common; or ordinary. For example "Prakash has AVERAGE excel skills".

Today I am trying to throw some light on AVERAGE function in excel but will also talk about AVERAGEA, AVERAGEIF & AVERAGEIFS.  So be with this post and by the time you finish reading, you'll be equipped with the power of four functions of excel in a matter of few minutes.

AVERAGE function as described in Excel Help is "Returns the average (arithmetic mean) of the arguments." For example, if the range A1:M1 contains numbers, the formula =AVERAGE(A1:M1) returns the average of those numbers.  See the image below explaining this situation:

Things one should know about AVERAGE function are provided arguments can either be numbers or names, ranges, or cell cell references that contains numbers.  Also if a range or cell reference contains text, logical values, or empty cells, those values are ignored; however, cells with negative or zero value are included for calculation of average.

Now what should one do if his data also has negative or zero values and he is looking to get average of only positive values.  Got stuck? Don't worry there is always a solution for every problem, and this riddle can also be solved.  You just need to know the way to approach it.  The formula created by using combination of SUMIF & COUNTIF as =SUMIF(A1:M1,">0")/COUNTIF(A1:M1,">0") can provide us a solution. If you've any other way to solve our problem, please share with our readers in comments at the end of the post.

Now I believe you guys know the AVERAGE function and are ready to go ahead and study other related fuctions I mentioned in the beginning.  First to go is AVERAGEA, this function also provides arithmetic mean but it doesn't ignore logical values and text in arguments.  Empty cell in the range is still ignored by this function as well.  For example in the below you see AVERAGEA(A1:M1) & AVERAGEA(A1:M1,N1) is giving us same result because blank cell N1 is ignored for calculation by function AVERAGEA.

It may also be possible that during your course of routine you would like to know or find average based on single or multiple criteria.  For example you want to calculate the average for numbers above 30 or average for values belonging to a particular category and are above 30 as well.  For situations like these excel has two powerful functions known as AVERAGEIF & AVERAGEIFS for single and multi criteria respectively.

In the image above AVERAGEIF function has been used to calculate average in the range A1:M2 provide that the values are greater than 30. At the same time AVERAGEIFS is being used to satisfy two conditions and calculate average.  Two conditions here are the number should belong to A2 category and should be greater than 30 as well.

So this way today you've added or refreshed functions related to averages in your knowledge base. Let me know about your past or present experiences of using these amazing functions in comments.  "HAPPY LEARNING"

Sum Using Multiple Criteria - SUMPRODUCT

SUMPRODUCT Function is used to sum a range when multiple conditions are met..

The first argument of sumproduct is the range which is to be summed.

Suppose you want to get a sum of a product range which falls between two Dates, In other words, the value in the amount range is summed only if both of the following criteria are met:

First condition: The Product should be Samsung..

Second condition: The range of the Date should be between 1-Jan-2001 & 22-Jan-2001

Here in this example my data is in Range A1:C38 & the conditions to be met are in E1:F3.

The below image is an extract from the worksheet.

The formula I have derived to get the solution is as follows:

=SUMPRODUCT((A2:A38>=F1)*(A2:A38<=F2)*(B2:B38=F3),C2:C38)

Put this formula and you will get the answer. This worksheet demonstrates summing based on multiple criteria. Download Sample File

This is a short and easy tip for time being, see you soon again.  "HAPPY LEARNING"



Insert a Blank Row after each Record without VBA

Hello friends I am back again with a new excel trick for you to learn.  Today I am trying to explain a very simple way to add a blank row after each record in your data.  This trick is very special because without VBA it is the only method to insert a blank row to your data except doing it manually.

Let me first tell you a little bit about the objective that I am planning to achieve. See the image below:

I want to insert a blank row after each record in my data. So there should be blank row between "Chris Smalling" & "Fabio" similarly between "Jonny" & "Nemanja". Now the challenge is to do it without using VBA codes. So here you go with the solution.

Enter a series of numbers in the column next to your data.  You can also use "Fill Handle" to put the series of numbers if your data is large, this is an efficient way of putting numbers.

Once you have numbers next to your data select the series and copy it. Go to the last number in the series and below it paste the full series again.

Now select your complete data along with the series entered. Here the series has been entered in column 2 and now sort it in ascending order with the column containing your number series.

Now if you see your original data has got a blank row after each record. This is what we were trying to achieve with this trick.  I know this can be done with a very simple VBA code, for my super efficient VBA loving friends I am posting the code in comments below.

I hope we you all have gained something out of this post. Your suggestions and feedback are most welcome. Till then "HAPPY LEARNING"

Dynamic Charts with Index & Match Function

Have you ever created a dynamic chart in excel? If yes, Kudos to you!  If not don't worry today you can master that skill in couple of minutes.  I would like to tell you that this is my first post on charts and I am starting a series on charting with this.  I will keep adding more to it time and again for sure.  In this post I'll be talking about how to create dynamic column chart using few amazing but simple functions of excel.  INDEX, MATCH & VALIDATION are three things I am using to create a dynamic chart which will look like the below example.

You can download the example from here! DYNAMIC CHART FILE

I am assuming that you have already downloaded the file from above.  In it you can see my main data is in range A1:E7, but using nested INDEX & MATCH functions I have created a new data for my chart to take in range A10:B16.

The challenge here is to change the data in range A10:B16.  If you see, you will notice that in cell H1 I have used data validation and it is giving me year for which I want my chart to plot the data. The data range A10:A16 has the name of car manufactures.  Using these two inputs I am pulling out data from main data.  The combination of functions I have used is as follows:

B11=INDEX($B$2:$E$7,MATCH($A11,$A$2:$A$7,0),MATCH($H$1,$B$1:$E$1,0))

Now I have dragged this function till B16 and I have my dynamic data ready with me.  It changes as I change the year in Cell H1.  Using this data series I have now created a chart.  To do it the steps are as follows:

Select your data > Insert > Charts > Column > 2-D Column > Clustered Column

You can now make adjustments as per your presentation requirement and give it a look as you want.  I have removed data labels from this chart and changed the color of each bar as well.  Just keep one thing in mind if you have the nested function mentioned above, rest of the work is simple.  You can use these charts in sales dashboards and many other places.  I use them it to show the performance of my team on weekly basis.

Dear reader, please let me know any feedback you have for me.  You can also give suggestions and topics that you want me to cover in upcoming posts.  Till then "HAPPY LEARNING!"

Sort Data from Left to Right (Sort data in Rows)

Dear readers & followers it's been a long time since my last post.  Now I have finally got some time to write something interesting and worth sharing. Today you're gonna learn how to sort data in rows. What I mean by that is sorting your data from left to right instead of sorting from top to bottom in column.

If you look at the image below it has few dates mentioned in column B to F and few figures for gross profit, tax & net profit associated with these dates.  Now what I am going to do is to sort these figures on the basis of dates in ascending order.

To do this what you'll have to do is follow a simple procedure and it will take very little time.  First of all select the data you want to sort and go to Home tab. There you'll find Sort & Filter option under Editing group.  Then you've to click on Custom Sort as shown in image below.

This will open a pop up box, at the top of it click on the Option button which will option Sort Options... box there look for Orientation and select Sort left to right.  By default it is Sort top to bottom.  This is shown in the image below.

Click OK and now you can see the option of sorting your data with rows.  Look at the sort by drop down option and you'll see Row1, Row2 etc now. Earlier it used to be Column1, Column2 and so on.

I've selected Row1, as my dates are in row 1 and I want to sort on their basis.  You can choose as per your requirement.  Oldest to Newest option under Order drop down will help us arrange the dates in required manner.  Click OK and here is what we intended to achieve.

Guys its easy and can be really helpful when you've to deal with large number of columns.  Hope you'll like my post, you can share your feedback or suggestion or any query via comments below. "HAPPY LEARNING"

Finding Top 5 Performers in Excel

I recently came across with a problem asked by one of my friend.  He wanted me to create a function in Excel to know the Top 5 sales performer in his team.  I suggested him a easy way of sorting the data in descending order based on sales figures but he wanted to keep the data intact and know the result.

See the image below to visualize what he was looking for:

To provide him with an answer I used three Excel function namely:

• INDEX(array, row_number, column_number)
• MATCH(value, arrray, match_type)
• LARGE(array, nth_position)

Using these three powerful fucntions in conjunction, I came out with a solution and want to share it with you as well. The formula goes like this

E2: =INDEX($A$2:$A$12,MATCH(LARGE($B$2:$B$12,D2),$B$2:$B$12,0),1)

I dragged the formula till E6 and I got the result required, my Top 5 Sales Performers !

Now let me explain the formula's used here, basically LARGE function helps us in finding k-th largest value in our sales data.  Say fourth or fifth largest sales figure.

Using the value given by LARGE function, we are using MATCH function to know the relative position of the value in our sales figures.

Lastly INDEX function is giving us the name of the Sales Person. That's it !!

I hope you'll get benefitted by using this formula. "HAPPY LEARNING"

SAMPLE FILE

MS Excel: Subtotal Function and Filter

The Subtotal function provide the subtotal of the numbers in a column.  It allows us to perform a calculation in the worksheet, and the same calculation can also be performed on the subsets of the data by applying filters. The magic of this function is that it ignores values in rows hidden by a filter.

The syntax for the Subtotal function is:

        Subtotal(function_num, ref1, ref2, ...)

function_num is the type of subtotal that you'd like to create. Image below explains 11 types which we can select:

ref1, ref2, ... are the ranges of cells that you want to subtotal.

Please go through the example with images below, it will help a lot in understanding Subtotal function.

Here I want to know the average monthly salary for each city. Normal AVERAGE function won't be helpful here because once we'll apply filter to it will still give us the average of all the records in the data. 

Now as per the requirement if I want to know the average monthly salary payable in Augusta region, the SUBTOTAL function will provide me the desired result while AVERAGE function will still give me the average of complete data.

I believe, I've not confused you and this post will be helpful to you while at work.  Let me know your feedback or suggestion.  I SHARE COZ I CARE! "Happy Learning"

Download SAMPLE FILE

VLOOKUP to fetch 2nd, 3rd or 4th Value

We all use VLOOKUP and must have observed a shortcoming that it only provides you with the first matching result. I mean if we have data where the lookup_value is coming twice or more times, VLOOKUP will give us the result for the first occurrence only. In the data below if your boss is interested in knowing the commission paid to "Mavericks Reality" for their second booking, how will you find that?

Solution will go like this:

You'll be a adding a helper column right next to broker's names and try to get a unique name. I am using COUNTIF function to do the trick for me like: =COUNTIF($B$2:B2,B2)&B2.

What this will do is, convert our lookup_values as 1Mavericks Reality, 2Mavericks Reality and so on (like in the image below). Now you have a unique lookup_value and you can simply use VLOOKUP("2Mavericks Reality",table_array,col_index) and you are done with your answer.

This is simple right?

You can download the sample file to get a better understanding of this tip. It tells you how you create formulae to get 2nd or 3rd occurrence. VLOOKUP FOR 2nd, 3rd OCCURRENCE.

Share this tip with everyone. "Happy Learning"

Filter Asterisk Character ("*")

Hello friends I hope you are keeping track of our Excel tips and learning new things everyday. Now it's time to get ready for another Excel tips. Today's post will be short and but very unique. Today we are going to learn, how to filter the asterisk character ("*"). Suppose you have a data which contains few asterisk character ("*") and you want to filter rows containing asterisk character ("*").

Now if you have been new to this situation you will simply say it's very easy. But mind it, It's not! There is a special technique to this and I'll take you step to step through it.

1. Apply filter to your complete data which contains asterisk ("*")

2. Click on filter drop down, Select Text Filters and than Contains...

3. It will open Custom AutoFilter pop-up box, here against the drop down with contains is another blank drop down. In this use Tilde ("~") before Asterisk ("*") and hit OK.

With this I wrap today's post, practise it and share it with everyone! "Happy Learning"

AND Function: Test Mathematical Conditions!

Moving ahead with our series of logical function, lets evaluate the AND function AND function provides result in TRUE or FALSE as output based on one of more condition. AND function evaluates at least one mathematical expression located in another cell in the spreadsheet.

The syntax it uses is as follow:

AND(logical1,logical2, ...)

Logical1, logical2, ...   are 1 to 255 conditions you want to test that can be either TRUE or FALSE.

Here is an example along-with image, it will help you understand the function in a better way.

I have few number in cell C1, C2 and C3 and I want to test certain mathematical conditions in cell D1 based on these numbers.

I have used the formula as follows

D1: =AND(C1>200,C2>200,C3>200)

If all three cells (C1,C2, and C3) has number greater than 200, the result in cell D1 will be TRUE. If any of these cells have value less than or equal to 200, the output for the AND function will be FALSE. "Happy Learning"

Explaining Array Formulas in Excel

There is special class of formulas available in Excel know as Array formulas or CSE b’coz they are entered by hitting three keys at a time Ctrl+Shift+Enter. There is no doubt this is one of the most powerful feature provided by Microsoft Excel, but this is one of least used feature as well. Array formulas empower you to calculate that you can’t perform with standard Excel function.  

The "Help" in Excel defines them as below:

"An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments."

What motivated me to write on this post is also an interesting story. I have few very brilliant excel users as colleagues, one day one of them told me that he is not aware about the usage of array function. I tried to explain my bit, but he was not convinced with my elucidation.  That made me feels very strange! And decide to dedicate my next post to Array formulas.

I will not go into the details of this function and its application but will surely make you aware how it functions! So let visualize the data given below: 

and if I want you to know the sum of sales, provided more than 6 units are sold for Event product.

The function will go like this G13: = SUM((D2:D11="Event")*(E2:E11>6)*(G2:G11)) and hit Ctrl+Shift+Enter you will get the solution.

So array functions are helpful when we have more than one decisive factor for our result.

Important Tip! If your formula is not giving you the result you want and you believe you have done everything correctly, you can display the calculation in any part of your formula. To do so, just highlight the condition supplied and hit F9. 

It will give you result how Excel is seeing your condition in TRUE or FALSE.  It will give TRUE when the condition applies and FALSE where it fails.

This is a handy tip to remember for troubleshooting the parts of any formula that are giving you problems.

Before concluding this post I would like you to know the number one rule with array formula, use them only when needed and know when & where to use them. I am not trying to de-motivate you for using array formulas but too many arrays will slow down the functionality of your workbook.

And at last I would request to give your feedback for me and share posts with your contacts and social group.  Let’s continue enriching this mutual relation and help grow each other. “Happy Learning”

Two ways LOOKUP formula to extract values from left & right (INDEX & MATCH FUNCTIONS)

I am sure if you’re using Excel at work, then you must have used lookup function (LOOKUP, VLOOKUP & HLOOKUP). There is no doubt that these are most useful & beautiful functions provided in the excel package. But if you haven’t used these function yet, let me take the honor of telling you that a lookup function return a value from a given table by looking up another value in the same table.

Because of our habit of entering data in very common style, VLOOKUP is among the most used function of Excel. But there is a limitation that it cannot go left and with all the LOOKUP functions is that they only look in the left column and return a from a cell to the right. So what I mean by this is LOOKUP function in Excel are useful for one-way lookups, there is function (inbuilt) for two-way lookup.

Today after reading this post you will be able to conquer this limitation, I am sure of that! First I would like you to look at the data given below and visualize it. It a table representing sales figures for ten of my employees in USA. I have named this table as “SalesData”.

Now if you want to know whose region is Minnesota, no lookup function will be useful. This is because, LOOKUP functions only return values to the right and it doesn’t return values from the left side. One transitory solution can be cut the data from column C and insert it in column and use VLOOKUP, but such quick fix are not always possible. Especially when you are not supposed to manipulate the arrangement of data provided.

In this case we will take help of two functions available in Excel namely INDEX & MATCH.

INDEX(reference,row_num,column_num,area_num): This function returns the value of an element in a table or an array, selected by the row and column number indexes. It means this function will provide you result from your data when you supply the row number and column number of the desired result.  The answer will be intersection of supplied row & column number.

MATCH(lookup_value,lookup_array,match_type): This function returns the relative position of an item in an array that matches a specified value in a specified order. It means this function will provide you the number where your lookup value falls in the series.

Now let’s come back to our problem where we have to know the name of the sales person responsible Minnesota region.

The solution will go like this F13:  =INDEX(SalesData[Name],MATCH(D3,SalesData[Region],0)) this will provide the name as “JAMES”

Similarly if you want to know who over achieved his target by highest margin? Solution will be F14: =INDEX(SalesData[Name],MATCH(MAX(SalesData[Difference]),SalesData[Difference],0)) and the name will be “EMMA”.

Now before wrapping the post here I have some homework for you. Please try to find the following using function mentioned above.

• Who has the maximum & minimum target in my team and what were their respective regions? For minimum use MIN function.
• What is the name of person managing Kansas region and how long is he/she with the organization?
• Who has the lowest target among the team?
• Who is the newest member of the team?

Let me know you solutions, you can post them in the comment section!

I hope you like this post and will share this with your friends and colleagues to help us grow the community of excel learners, there are various options you can use to share with people at the end of the post.

Now you also follow me on Twitter, just click on the vertically aligned Twitter radio button to the left of your computer screen to follow!

You can post your feedback as a comment below and I will take a note of it for future. “Happy Learning”

Please download the file from here!

Create a Table in Excel 2007

With the launch of Office 2007, Microsoft has introduced a rich concept to Excel know as "Table". Table in Excel 2007 help you to manage and analyze your data. This feature has less functionality in terms of calculation but it is very useful in storing lots of information consistently. Formatting, sorting and filtering are very easy with Table. Typically, an Excel table has only column headings and no row headings.
 

I would recommend you to organize your data before creating a Table. Few points which you keep in mind are as follows:

• Your data should have a proper and unique heading in column
• There should be no blank row or column in your data
• Your data should be separated with any other information by at least one row and one column

How to create a Table?

Creating a table is very easy in excel.

1. Select the cells that contain data

2. On the Ribbon, click the Insert tab and in the Tables group, click the Table command

3.Click OK and you are done with a default style table

Special functionality of a Table 

• Autofilter and Sort: When you create a Table it provides you with integrated Autofilter and Sort functionality to your data. You don't have to add this feature manually, only this that is required to make it more helpful is your data should have headings in each column.
• Header names while scrolling: If your data is large and it doesn't fit your screen while scrolling new Excel has feature that temporarily replace column letters with the table's column names.
• Automatic expansion of data: When you add new data in row or column of your table, Excel expands the range of your table and takes it as part of your table.
• Automatic formatting: When you add or delete any row into the table it automatically formats the change. You don't have to make edits to the changed data Excel Table will do it for you! 

I hope you find my posts helpful and easy to learn at the same time. Please do share the tips with your friends, colleagues and if you have any feedback for me I will look forward to it. 

"Happy Learning"

Excel Function: IF ( logical_test, value_if_true, value_if_false )

Now in coming two weeks I have decided to discuss functions available in the stable of Microsoft Excel.  I have taken Logical function to start with, as I found them most useful will working data (this is my personal view :) though). To start with I am gonna explain IF function with an example.

The IF function is one of Excel's logical functions and it tests to see if a certain condition in a spreadsheet is true or false.

The syntax for the IF function is:

=IF (logical_test, value_if_true, value_if_false)

logical_test    - a value or expression that is tested to see if it is true or false.
value_if_true  - the value that is displayed if logical_test is true.
value_if_false - the value that is displayed if logical_test is false.

Some of the conditional operator you need to know:
<   - Less Than
>= - Greater than Or Equal To
<= - Less than Or Equal To
<> - Not Equal To

The following two images will tell you how we can use IF function. In cell A1 have value 15 and I want to populate test "Good" or "Bad" based on this value. For example if value is greater than 10 then it is "Good" else "Bad".

 

Formula which I have entered in cell A2: =IF(A1>10,"Good","Bad")

Similarly you can see cell B2 is giving me "Bad" because A2 is less than 10.

You can use this many ways and under numerous conditions. Few days back one of my teacher friend came to me and we were discussing how we can use Excel to help him in work. He gave me a situation where based on a number he wants to assign grades to his student. The parameters to grades were as follows:

A If the student scores 85 or above
B If the student scores 65 to 84
C If the student scores 50 to 64
D If the student scores 33 to 49
FAIL If the student scores below 33

An example of this data looks like this:
To solve his problem I used IF Function. I created a similar table below his data and wrote a formula as B12: =IF(B2>=85, "A", IF(B2>=65, "B", IF(B2>=50, "C", IF(B2 >=33, "D", "Fail" ) ) ) )

I draged the formaula cross the data sheet and got my result.

I hope this is clear to everyone. I know many of you are already using this function.  Kindly let us know how do you use this function in your daily life.  You can post your experiences and example in the comment box, it will help us in learning new things.  "Happy Learning"

Pass this to your friends and colleagues this may also help them! 

To download the example file click here