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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

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

4th should have been 3rd

Sorry!

--
Kind regards,

Niek Otten

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

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.

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

Thanks, It worked!

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

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