software

I am quot;tryingquot; to build a spreadsheet at work illustrating a gain/share
model that is triggered off of revenue amp; 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 quot;discountquot; 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: www.excelforum.com/member.php...oamp;userid=29743
View this thread: www.excelforum.com/showthread...hreadid=494600
Hi, if your using a VLOOKUP formula try finishing your formula with a
quot;1quot; instead of quot;0quot; or quot;TRUEquot; instead of quot;FALSEquot;

HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=494600Sort 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

quot;wmaughanquot; gt; wrote in
message ...
gt;
gt; I am quot;tryingquot; to build a spreadsheet at work illustrating a gain/share
gt; model that is triggered off of revenue amp; volume in work. Given that
gt; information, here is one part of the spreadsheet I am having
gt; complications with.
gt;
gt; For a plug number I have my current revenue set at 13,000,000. If i
gt; was to receieve an additional 200,000 in work next year the following
gt; year (13,200,000), that would make a revenue increase of 1.5% (VALUE #)
gt; from the previous year. Based off of the the percentage of increase, I
gt; would like to offer the customer a quot;discountquot; capped at a certain
gt; percentage.
gt;
gt; % Of Increase (A) Discount Given (B)
gt; 50% 0.50%
gt; 40% 0.40%
gt; 30% 0.30%
gt; 20% 0.20%
gt; 10% 0.10%
gt; 0% 0.00%
gt; -10% -0.10%
gt; -20% -0.20%
gt; -30% -0.30%
gt; -40% -0.40%
gt; -50% -0.50%
gt;
gt; Right now I have the formula working to where the discount is given
gt; _IF__the percentage is exactly what is represented under the % of
gt; increase column... I would like for it to recognize the nearest
gt; (rounded down) percentage and return the appropriate discount, capped
gt; at .5%.
gt;
gt; Any advice would be appreciated.
gt;
gt;
gt; --
gt; wmaughan
gt; ------------------------------------------------------------------------
gt; wmaughan's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29743
gt; View this thread: www.excelforum.com/showthread...hreadid=494600
gt;

Tried that, for some reason it returns .5% regardless of the percentage
increase.--
wmaughan
------------------------------------------------------------------------
wmaughan's Profile: www.excelforum.com/member.php...oamp;userid=29743
View this thread: www.excelforum.com/showthread...hreadid=494600lt;and MATCH(), the latter with -1 as 4th argumentgt;

4th should have been 3rd

Sorry!

--
Kind regards,

Niek Otten

quot;Niek Ottenquot; gt; wrote in message
...
gt; Sort your table Descending. Omit the fourth argument of the VLOOKUP (you
gt; probably did that already)
gt;
gt; If you can't sort your table for whatever reason, use a combination of
gt; INDEX() and MATCH(), the latter with -1 as 4th argument
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;wmaughanquot; gt; wrote
gt; in message ...
gt;gt;
gt;gt; I am quot;tryingquot; to build a spreadsheet at work illustrating a gain/share
gt;gt; model that is triggered off of revenue amp; volume in work. Given that
gt;gt; information, here is one part of the spreadsheet I am having
gt;gt; complications with.
gt;gt;
gt;gt; For a plug number I have my current revenue set at 13,000,000. If i
gt;gt; was to receieve an additional 200,000 in work next year the following
gt;gt; year (13,200,000), that would make a revenue increase of 1.5% (VALUE #)
gt;gt; from the previous year. Based off of the the percentage of increase, I
gt;gt; would like to offer the customer a quot;discountquot; capped at a certain
gt;gt; percentage.
gt;gt;
gt;gt; % Of Increase (A) Discount Given (B)
gt;gt; 50% 0.50%
gt;gt; 40% 0.40%
gt;gt; 30% 0.30%
gt;gt; 20% 0.20%
gt;gt; 10% 0.10%
gt;gt; 0% 0.00%
gt;gt; -10% -0.10%
gt;gt; -20% -0.20%
gt;gt; -30% -0.30%
gt;gt; -40% -0.40%
gt;gt; -50% -0.50%
gt;gt;
gt;gt; Right now I have the formula working to where the discount is given
gt;gt; _IF__the percentage is exactly what is represented under the % of
gt;gt; increase column... I would like for it to recognize the nearest
gt;gt; (rounded down) percentage and return the appropriate discount, capped
gt;gt; at .5%.
gt;gt;
gt;gt; Any advice would be appreciated.
gt;gt;
gt;gt;
gt;gt; --
gt;gt; wmaughan
gt;gt; ------------------------------------------------------------------------
gt;gt; wmaughan's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=29743
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=494600
gt;gt;
gt;
gt;

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 quot;-1quot;, it would not
reference the data.

I'm sure I am missunderstanding you, and I do not have much experience
with the quot;MATCHquot; function.--
wmaughan
------------------------------------------------------------------------
wmaughan's Profile: www.excelforum.com/member.php...oamp;userid=29743
View this thread: www.excelforum.com/showthread...hreadid=494600
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: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=494600
Thanks, It worked!--
wmaughan
------------------------------------------------------------------------
wmaughan's Profile: www.excelforum.com/member.php...oamp;userid=29743
View this thread: www.excelforum.com/showthread...hreadid=494600
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: www.excelforum.com/member.php...oamp;userid=29743
View this thread: www.excelforum.com/showthread...hreadid=494600
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: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=494600