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% .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.
Bookmarks