How to calculate bonus using if or vlookup function?
HI, IM VERY NEW IN EXCEL AND I'VE BEEN GIVEN A TASK IN MY E-LEARNING CLASS ABOUT CALCULATING BONUS IN THE TEMPLATE GIVEN IN THE ATTACHMENT.
THE THING THAT I COULDNT UNDERSTAND IS TAT I WAS TOLD TO CALCULATE THE YEARLY BONUS USING FUNCTION "IF" OR "VLOOKUP". I DO NOT KNOW HOW TO CONNECT THOSE DATA IN THE WORKBOOK. I HOPE SOMEONE CAN GIVE ME SOME GUIDELINES N TIPS FOR BEGINNERS LIKE ME. I'VE ALSO MENTION THINGS THAT I DON'T UNDERSTAND IN THE WORKBOOK.
IN THE TASK INFORMATION GIVEN IS:
CATEGORY 1- SALARY $1000 & BELOW, BONUS GIVEN $2000+75% SALARY
CATEGORY 2- SALARY $1001-$5000, BONUS GIVEN $2000+50% SALARY
CATEGORY 3- SALARY $5000 & ABOVE, BONUS GIVEN $2000+25% SALARY
regards
IF & VLOOKUP.xlsxfirst you need to name the range that the vlookup will be looking at, "A16:E19". you will select all of these cells then go to the upper left hand side of the sheet and you will type in a name in the name box. then you can do an if statement.
IF(D7<1001,"0-1000",IF((D7>1001)*(D7<5001),"1001-5000",IF((D7>5000)*(D7<6000),"5001-6000"))) enter this in e2, then you can do a vlookup in f2 off of these numbers, VLOOKUP(E2,range,2) this will give you the percents of bonus. then you can replicate this formula for each column as you move along and only change the number after "range" to look in the new column of your named range to give the corresponding value you need.
Last edited by bryanbak3; 01-18-2012 at 02:48 PM.
STOP! CONTINENTAL DRIFT.
Aren't you supposed to do your homework yourself?
Cheers - THE WARNING I RECEIVED WAS NOT JUSTIFIED
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks