Vlookup Rounding
 

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.


--
wmaughan
------------------------------------------------------------------------
wmaughan's Profile: http://www.excelforum.com/member.php...o&userid=29743
View this thread: http://www.excelforum.com/showthread...hreadid=494600

Vlookup Rounding
 

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

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494600

Vlookup Rounding
 

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
--
Kind regards,

Niek Otten

"wmaughan" wrote in
message ...

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.


--
wmaughan
------------------------------------------------------------------------
wmaughan's Profile:
http://www.excelforum.com/member.php...o&userid=29743
View this thread: http://www.excelforum.com/showthread...hreadid=494600

Vlookup Rounding
 

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


--
wmaughan
------------------------------------------------------------------------
wmaughan's Profile: http://www.excelforum.com/member.php...o&userid=29743
View this thread: http://www.excelforum.com/showthread...hreadid=494600

Vlookup Rounding
 

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

4th should have been 3rd

Sorry!

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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
--
Kind regards,

Niek Otten

"wmaughan" wrote
in message ...

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.


--
wmaughan
------------------------------------------------------------------------
wmaughan's Profile:
http://www.excelforum.com/member.php...o&userid=29743
View this thread:
http://www.excelforum.com/showthread...hreadid=494600

Vlookup Rounding
 

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.


--
wmaughan
------------------------------------------------------------------------
wmaughan's Profile: http://www.excelforum.com/member.php...o&userid=29743
View this thread: http://www.excelforum.com/showthread...hreadid=494600

Vlookup Rounding
 

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
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494600

Vlookup Rounding
 

Thanks, It worked!


--
wmaughan
------------------------------------------------------------------------
wmaughan's Profile: http://www.excelforum.com/member.php...o&userid=29743
View this thread: http://www.excelforum.com/showthread...hreadid=494600

Vlookup Rounding
 

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 a
formula that allow me to do the following.

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


--
wmaughan
------------------------------------------------------------------------
wmaughan's Profile: http://www.excelforum.com/member.php...o&userid=29743
View this thread: http://www.excelforum.com/showthread...hreadid=494600

Vlookup Rounding
 

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

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494600