Growth rate discount

[questionmark1980]

Hi all

I would like your help in relation to a formula for a growth scaling discount. In the below example customer X should be getting a zero discount, customer Y should be getting zero, customer Z should be getting 1.50% and customer S 2.50%. Which formula should I be using in order to reflect correct results?

Thanks a lot in advance!!

		Growth 0% ->1%	Growth 1.01% -> 3%	Growth 3.01% and over
	Growth	0%	1,50%	2,50%
Customer X	-10%
Customer Y	1%
Customer Z	2%
Customer S	4%

<colgroup><col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="64" style="width: 48pt;" span="2"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <tbody> </tbody>

 

[JeanKim]

discount of what value?
where is the value you are discounting?

 

[Tetra201]

Try this:

=LOOKUP(B4-0.0001,{-9.99E+307,0.01,0.03},{0,0.015,0.025})

 

[Marcílio_Lobão]

questionmark1980, Good morning.

	A	B	C	D	E
1	.		Growth	Growth	Growth
2			0.00%	1.01%	3.01%
3			1.00%	3.00%	and over
4	Customer Name	Growth	0%	1,50%	2,50%
5	Customer X	-10%	formula
6	Customer Y	1%
7	Customer Z	2%
8	Customer S	4%

<tbody>
</tbody>

Try to use:

C5 -->
=IF(IFERROR(HLOOKUP($B5, $C$2:$E$4, 3, TRUE), 0)=C$4, C$4, "")

Copy it down and to the right

Please, tell us if it worked for you.
I hope it helps.

 

[questionmark1980]

Sorry for the late reply but I did not have access on internet.
@ Marcilio Lobao This formula is also helpful but what if I had to apply the % achievement on a turnover. So lets say that customer Z has an annual turnover for current year of 100,000 and based on growth he needs to be getting 2% discount. What would be the formula that I would need to type in so as to get the discount value in cell D7 and in the rest of the cells to get zero?

Thanks a lot in advance and sorry for the late reply.