• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup Complications Percentages

C

craymer

New Member
I am an Excel novice, but enjoy learning through websites like Chandoo.org. We own several Jimmy John's and I am attempting to build a spreadsheet for calculating a manager's bonus based on four performance based categories. I can't get the vlookup function to work correctly and I think it is because of the percentages. I am calculating an average for each category and looking up the bonus for that percentage in a table. In particular, I can't get the "Audit" category to work. Your help is greatly appreciated!


P.S. Can I not attach the file?
 
Craymer


Firstly, Welcome to the Chandoo.org Forums


Can you please take 5 mins to read the Green Sticky posts at: http://chandoo.org/forums/
 
Hi, craymer!


Your formula at cell G7 is:

=VLOOKUP($F$7,Tables!$G$3:$H$70,2,1)

where the last parameter in 1 indicates an approximate search into a range in ascending order, which is correct but only up to row 63, then zeros appear and Excel gets lost.


However don't ask me why it works fine with $H$64 and not with 65 in advance, but maybe here's a workround:


In worksheet Data:


a) Change your G7 formula to:

=VLOOKUP($F$7+0.000001,Tables!$G$3:$H$70,2,1)


In worksheet Tables


b) Change your G4:G70 formulas to:

=G4+0,001


c) Change your H4:H70 formulas to:

=H3+SI(Y(G4<=Data!$E$17;G4>0);$P$5;0) -----> in english: =H3+IF(AND(G4<=Data!$E$17,G4>0),$P$5,0)


d) Now back to what you were asked to don't ask me, place this 2 temporary formulas in:

L8: =REDONDEAR(G8;3) -----> in english: =ROUND(G8,3)

M8: =BUSCARV(L8;G$3:H$70;1;FALSO) -----> in english: =VLOOKUP(L8,G$3:H$70,1,FALSE)

and copy them down up to row 70.

L8 is the same argument used in the Data!G7 cell formula, and M8 is an exact search for the "same" theoretical value in G column... but check what happens from row 41 in advance (values from .938)... Yeap, Excel doesn't find those "exact" values shown with "two" decimal places. In fact this is due to how Excel stores internally non integer values, i.e., single or double numeric type values. Give a look at the following links:

http://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

http://www.cpearson.com/excel/rounding.htm


Hope it helps.


Regards!
 
Hi ,


I would like to make just one point ; Excel is a tool for making calculations / processing data easier ; on its own , it is not going to make things right.


It would be better to write down exactly what you wish to do , and then start putting in the formulae to do it.


Going through your calculations , what I can understand is :


1. Your data table 1 in the Tables tab , in the range M2:p6 is independent of the employee , which means it is the same for all managers.


2. Your data table 2 in the Data tab , in the range B15:E18 is dependent on the employee , and may vary from one manager to another. You will manually enter it for each manager.


3. Each manager's performance bonus is decided by where he / she falls within these Low / High limits ; as far as I can see , in the case of the Labor and Food categories , less is better ; in the case of the Audit and Samples categories , more is better.


4. Therefore , each manager's bonus is decided on a pro-rata basis , based on the achievement in each of the categories ; the increment is mentioned in the data table 1 , and it should be fairly straightforward to calculate the bonus based on the average achievement figures in column F ; you don't need the tables in columns A through K in the Tables tab.


One thing which I do not understand is the divide by 13 ; can you explain this ?


Narayan
 
I apologize it has taken me so long to respond, I had to temporarily table the project for other responsiblities.


SirJB7, your solution worked until it got to Tables,G64. It returns $3,600, the highest bonus, for all zero values. This technically doesn't matter because vlookup will never find zero. However, it doesn't make since because the if and statement should return zero if it doesn't meet both conditions.


Narayank991, you are correctly understanding the workbook. I am intrigued on how I could accomplish this without the tables in A through K.


Thanks again for your help!


Chad
 
Hi Chad ,


The following formula return the same results as your VLOOKUP formulae :


In G5 : =ROUND(IF(F5<E15,Tables!$N3,IF(F5>D15,0,Tables!$N3*(D15-F5)/(D15-E15)))/13,2)


In G6 : =ROUND(IF(F6<E16,Tables!$N4,IF(F6>D16,0,Tables!$N4*(D16-F6)/(D16-E16)))/13,2)


In G7 : =ROUND(IF(F7>E17,Tables!$N5,IF(F7<D17,0,Tables!$N5*(F7-D17)/(E17-D17)))/13,2)


In G8 : =ROUND(IF(F8>E18,Tables!$N6,IF(F8<D18,0,Tables!$N6*(F8-D18)/(E18-D18)))/13,2)


Narayan
 
You must log in or register to reply here.
Back
Top