HOW TO USE MULTIPLE IF IN EXCEL

HOW TO USE MULTIPLE IF IN EXCEL

Using multiple IF statements in Excel

This tutorial will tell you, How to use multiple IF in excel.

The IF() function in Excel allows you to evaluate a logical condition which has two possible results  and calculate a different value for each result. However, sometimes you need to work with conditions where there are more than two possible results. That’s where multiple, or nested, IF functions come in handy. In this tutorial we’ll cover how to use nested IF functions to manipulate a logical condition.

This tutorial assumes you are already familiar with the IF function. If you aren’t, you can learn how to use IF statements here.

Remember that the IF function works by evaluating a logical test – a calculation which can only be TRUE or FALSE.  It  includes a calculation to perform if the logical test is TRUE, and another calculation if it is FALSE.

Nested IF functions work by replacing one or both of the TRUE/FALSE calculations with another IF function.

This formula can be confusing  for first time. Let’s have a look on the syntax & example of multiple/nested IF.

=IF(B2=”APPLE”, “FRUIT”, IF(B2=”TOMATO”, “VEGETABLE”, IF(B2=”SALT”, “SPICES”, “”)))

nested-if

Note :-

If you’ll type TOMATO in B2 then the C2 will change to VEGETABLE , Or if you type SALT in B2 then C2 value will be change to SPICES.

 

Another example of nested IF which calculates the grade of different persons according to score.

nestif

=IF(C4<64,”F”,IF(C4<73,”D”,IF(C4<85,”C”,IF(C4<95,”B”,”A”))))

Here is three nest IF IN this formula which calculate the grade.

So now if you change the score in column C then the grades will be change accordingly.

HOW TO USE VLOOKUP 

HOW TO AND FUNCTION 

 

HOW TO LEARN EXCEL

HOW TO LEARN EXCEL

Excel is a most powerful spreadsheet and data analysis application
These days, every job requires advance Excel skills. An organization wants these Excel skills –creating tables, format data, manipulate basic essential operations like(sum, subtraction, multiplication, division, average & summaries with filters and formulas, highlight data with conditional formatting, validation, charting , advance formulas and much more another thing which you will learn from here.

HOW TO BECOME AN EXCEL EXPERT
Excel is a huge application in itself with thousand of features and formulas.Formulas is back bone the ms-excel.
To become a good excel operator, very firstly you’ll have to learn about basic commands and formulas of excel.

http://www.dreamstime.com/royalty-free-stock-photo-3d-learn-here-crossword-image21000275

BASICS OF EXCEL :-


What is a workbook
what is a sheet
what is a cell
what is cell address

Create a new blank workbook

Explore the Excel interface
Move within worksheet cells
Enter data
Select and format data
Copy and paste formatting and data
Edit data
Insert and resize columns
Save a workbook in a new folder
How to cut,copy & paste commands works
How to find the specific name/text

How to protect file or sheet: Encrypt an Excel file with a password so that it requires a password to open it.
How to hide or unhide columns/rows

 

Keyboard Shortcuts: Keyboard shortcuts allow you to do things with your keyboard instead of your mouse to increase your speed.
shortcut commands of excel like ( ctrl +c, ctrl+v, ctrl+x, ctrl+z, ctrl+y, ctrl+s etc)

Other shortcut keys in excel

The basic formulas of excel is here :-
1- How to insert or delete a column/row
2- How to add two numbers in excel
3- How to subtraction/ division/multiplication in excel

4- How to apply conditional formatting 

5- How to use filters

6- How to use VLOOKUP function 

7- How to use multiple IF statement

8-How to use AND

 

VLOOKUP FUNCTION SYNTAX IN EXCEL

What is VLOOKUP

VLOOKUP is a most useful formula in Excel. Unfortunately – it is also one of the most confusing for the beginner. In simple words, actually VLOOKUP function search a value in the range which mentioned in the formula.

The VLOOKUP function can sometimes be a better solution in a scenario. You can see a really good example of where you should learn how the VLOOKUP function works in one of the comments below.
That’s not to say that VLOOKUP is automatically a better solution:

According to Excel’s formula description, VLOOKUP  looks for a value in the workbook, worksheet, table, array or in a column of a table, and then returns a specific value in the same row from a column you specify.

HOW VLOOKUP WORKS

If you want to pick up a value from other sheet then VLOOKUP function is the most useful for that.

To dumb it down for you, VLOOKUP lets you pull information about your selected cells into your current sheet, from other sheets or workbooks where that value exists.

Example:

The Formula

The formula for VLOOKUP looks like this:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Actual figure of function will look like this.

=VLOOKUP(A2,MASTER,2,FALSE))

 

VLOOKUP WITH IF STATEMENT

=IF(A2=””,””,VLOOKUP(A2,MASTER,2,FALSE))

 

 vlokup

What this formula says :-

=IF(A2=””,””,VLOOKUP,A2,MASTER,2,FALSE))

It’s mean if A2 have any value then search that value in cloumn 2 of MASTER sheet, FALSE uses for exact value.

 

VLOOKUP WITH MATCH                                                                                

This table is used to find a value based on a specified name and month.                                                   

The =VLOOKUP() is used to scan down to find the name.                                                                             

The problem arises when we need to scan across to find the month column.                                                     

To solve the problem the =MATCH() function is used.                                                                                  

The =MATCH() looks through the list of names to find the month we require. It then calculates 

the position of the month in the list. Unfortunately, because the list of months is not as wide                   

as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is added to compensate.                                                                            

The =VLOOKUP() now uses this =MATCH() number to look across the columns and picks out the correct cell entry.                                                                                 

The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the row headings are not sorted.        

 

 

What is a workbook

What is a workbook ?

Actually an Excel workbook is a file that contains one or more worksheets.
When you open ms-excel in your PC then a workbook generates itself, when a workbook a generated it has three worksheets by default. (sheet 1, sheet 2 and sheet 3) you can rename a worksheet according to you.You can also add or remove worksheets according to your need.

What is a worksheet ?

An excel worksheet is platform where you can enter your data and information or records, an excel worksheet is a spreadsheet which contains much more columns and rows , that you can use to organize various kinds of related information.

workbook-and-worksheet

 

How to learn Excel ?

HOW TO LEARN EXCEL

EXCEL LEARNING

How to learn excel .Excel is a most use full tool of ms-office.
These days, every job requires advance Excel skills. An organization wants these basic Excel skills –creating tables, format data, manipulate basic essential operations like(sum, subtraction, multiplication, division, average & summaries with filters and formulas, highlight data with conditional formatting, validation, charting , advance formulas and much more another thing which you will learn from here.

HOW TO BECOME AN EXCEL EXPERT
Excel is a huge application in itself with thousand of features and formulas.Formulas is back bone the ms-excel.
To become a good excel operator, very firstly you’ll have to learn about basic commands and  basic formulas of excel.


BASICS OF EXCEL :-
What is a workbook
what is a sheet
what is a cell
what is cell address
How to cut,copy & paste commands works
How to find the specific name/text
How to hide or unhide columns/rows
shortcut commands of excel like (crtl+c , ctrl+v , crtl+x , crtl+z , crtl+y , crtl+s etc)

smtemp

The basic formulas of excel is here :-
1- How to insert or delete a column/row
2- How to add two numbers in excel
3- How to subtraction/division/multiplication in excel


WHAT IS CELL & CELL ADDRESS

A cell is an intersection between a row and a column in a spreadsheet.Each cell in a spreadsheet can contain any value that can be called using a relative cell reference or called upon using a formula.


A “cell reference” means the cell to which another cell refers. For example, if in cell A1 you have =B1. Then A1 refers to B1. 


 

HOW TO CUT, COPY & PASTE IN EXCEL

These are  commands which must use in excel. Cut,copy,paste command is so simple.

Control+c = copy

Control+x = cut

Control+v= paste

Other useful commands in excel


 

What is a Workbook ?

What is a Worksheet ?