Saturday, April 18, 2015

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)

Saturday, April 11, 2015

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.



Tuesday, April 7, 2015

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
 
Thanks
 
 


Tuesday, March 31, 2015

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
 
 
 

The Great Man and Signature in India - Vol 1

Mohandas Karamchand Gandhi Dr. B. R. Ambedkar   Subhas Chandra Bose Sardar Vallabhbhai Patel Jawaharlal...