Vlookup Rounding

[wmaughan]

I am "trying" to build a spreadsheet at work illustrating a gain/share
model that is triggered off of revenue & volume in work. Given that
information, here is one part of the spreadsheet I am having
complications with.

For a plug number I have my current revenue set at 13,000,000. If i
was to receieve an additional 200,000 in work next year the following
year (13,200,000), that would make a revenue increase of 1.5% (VALUE #)
from the previous year. Based off of the the percentage of increase, I
would like to offer the customer a "discount" capped at a certain
percentage.

% Of Increase (A) Discount Given (B)
50% 0.50%
40% 0.40%
30% 0.30%
20% 0.20%
10% 0.10%
0% 0.00%
-10% -0.10%
-20% -0.20%
-30% -0.30%
-40% -0.40%
-50% -0.50%

Right now I have the formula working to where the discount is given
_IF__the percentage is exactly what is represented under the % of
increase column... I would like for it to recognize the nearest
(rounded down) percentage and return the appropriate discount, capped
at .5%.

Any advice would be appreciated.

 

[pinmaster]

Hi, if your using a VLOOKUP formula try finishing your formula with a
"1" instead of "0" or "TRUE" instead of "FALSE"

HTH
JG

 

[Niek Otten]

Sort your table Descending. Omit the fourth argument of the VLOOKUP (you
probably did that already)

If you can't sort your table for whatever reason, use a combination of
INDEX() and MATCH(), the latter with -1 as 4th argument

 

[wmaughan]

Tried that, for some reason it returns .5% regardless of the percentage
increase.

 

[Niek Otten]

<and MATCH(), the latter with -1 as 4th argument>

4th should have been 3rd

Sorry!

 

[wmaughan]

I have copied my formula below, please let me know if I am understanding
you correctly:

=VLOOKUP(F7,J19:L29,3,FALSE)

by changing the 3rd part of the equation, I am changing the LOOKUP /
Index Number. If I were to change that to a "-1", it would not
reference the data.

I'm sure I am missunderstanding you, and I do not have much experience
with the "MATCH" function.

 

[pinmaster]

Try one of the following:

=VLOOKUP(A1,your_table,2,1) *table sorted in descending order
=INDEX(B:B,MATCH(A1,A:A,-1)) *table sorted in ascending order

HTH
J

 

[wmaughan]

Thanks, It worked

 

[wmaughan]

Thanks for all the help, I was able to overcome one of my problems
which leaves me with one final question. I am curious if there is
formula that allow me to do the following.

I would like to offer a productivity Gain/Share model if work i
completed ahead of schedule. For every 5% increase in production
would like a .5% revenue gain, with unlimited upside. For every 5
loss in production I would take a .5% deduction limited at 4%. I
there one formula that could do this? Thanks again for all your hel

 

[pinmaster]

I think I will let the MVP's handle that one. It might be best to star
a new thread with some details as to how your worksheet is setup an
how you measure increase in productivity.
Good luck!

Regards
J