Use Nested IF & VLOOKUP formula for Grading

 
Example :

		GRADE
Student	Score	Nested IF	Vlookup Table	Vlookup Constant		Score	Grade
Billy	58	F	F	F		0	F
Tamra	64	D	D	D		60	D
Cheslan	83	B	B	B		70	C
Doug	72	C	C	C		80	B
Nicole	93	A	A	A		90	A
Fran	89	B	B	B
Lester	77	C	C	C
	Nested IF:	=IF(Score>=90,"A",IF(Score>=80,"B",IF(Score>=70,"C",IF(Score>=60,"D","F"))))
	VLOOKUP Table:	=VLOOKUP(B3,$G$3:$H$7,2,TRUE)
		=VLOOKUP(B3,GradeLU,2,TRUE)
	Vlookup Constant:	=VLOOKUP(B3,GradeLookup,2,TRUE)

How to using PMT Formula in excel ( For LOAN EMI Calculation)

If you calculate what is your EMI for the loan please see below :

Example : 1

Loan Amount : 50000

Interest : 5 %

Type of Int. : Yearly (/12)

Total paid months : 60

Now Calculate as per below :

=PMT(5%/12,60,50000)

Now result is : 943.56 Rupees per month

 

If you calculate in the requirement as quarterly interest, and EMI paying in quarter beginning so how many rupees see example EMI as per below :

Example : 2

Loan Amount : 10000

Interest : 3.5 %

Type of Int. : Quarterly (/4)

Total paid months : 24

Quarter : 8

Pay in beginning : 1

Now Calculate as per below :

=PMT(3.5%/4,8,10000,5000,1)

Now result is : 1,889.30 Rupees per month

 

I hope helpful this post for you.

How to use VLOOKUP formula in Excel

Source Table as per below

 

 

First Check Source Table Column Heading & Required Data Table Column Heading Same ?

If same so type formula as see per below table in function bar or not so please change and match column heading

( Vlookup Rule : Required Source Table Heading & Destination Table Heading are same) now:

 

 

in the formula " =VLOOKUP(F2,A1:D1,4,FALSE) "

 

F2 is lookup value

A1:D1 is Table Array (Source Table)

4 is Column Number

FALSE is exact match

 

Insert in Vlookup value cell is check & find value in table array, now select in table array value is source table lookup value find in this table, now insert column number for are you getting value as per your requirement and now select FALSE for exact matching value. ENTER

Most important point select Table Array range & press F4 for fixed table address otherwise all the getting data is wrong

Now enter & drag for the all the result

 

I hope I was present VLOOKUP formula easily, if any suggestion please mail on

malekjavedkhan@gmail.com

 

Thanks

 

 



Using INDEX & MATCH formula in excel for multiple value comparison:

Example:

Source Table Array as per below:

Employee Name	Basic	DA	Trans	PF	Total
Joseph	5000	2500	1000	600	9100
McCulum	7000	3500	1000	840	12340
Denial	9000	4500	1000	1080	15580
Desosa	12000	6000	1000	1440	20440
Rechard	15000	7500	1000	1800	25300

Now same formula add in every cell only column no. change as per required data

 

See Above formula in formula bar I have using multiple value comparison and given Total