Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Rounding criteria within a nested vlookup and hlookup | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |