Avoiding Long If Statements

[Rodrigo]

Hi all,


How does one avoid long if statements. The problem is as follows:


I'm trying to correlate an incentive fee (IFee) to a predetermined level of profit before tax (PBT).


If PBT<=5E6, the IFee = 10% of PBT

If PBT >5E6<=10E6, the IFee = 10.5% of PBT

If PBT >10E6<=15E6, the IFee = 11.5% of PBT

If PBT >15E6<=20E6, the IFee = 13.5% of PBT

If PBT >20E6<=30E6, the IFee = 17.5% of PBT

If PBT >30E6<=50E6, the IFee = 20.0% of PBT

If PBT >50E6, the IFee = 25% of PBT


Can one name a formula and reference the latter within the if statement?


Regards


Rodrigo

 

[vijaySharma]

Rodrigo,


Have you tried the CHOOSE function...


~Vijay

 

[PSG]

You can do this by using VLOOKUP...... if you want!


ColA ColB

PBT IFee

0.1PBT 0.1IFee

5PBT 0.105IFee

10PBT 0.115IFee

15PBT 0.135IFee

20PBT 0.175IFee

30PBT 0.2IFee

50PBT 0.25IFee


If your PBT is in cell B11 and you need the result in Cell C11 you can use C11: =VLOOKUP(B11,B3:C9,2,TRUE)


hope this works!

 

[Luke M]

Even simpler, a LOOKUP formula. You can either write out the table (preferred) or include it in the formula like so:

=LOOKUP(PBT,{0,5E6,10E6,15E6,20E6,30E6,50E6},{.1,.105,.115,.135,.175,.2,.25})*PBT

 

[Rodrigo]

Thank you all for the input. I would like to put a spin on the previous problem, let me explain:

If PBT <=5E6, the IFee = 10% of PBT

If PBT <=10E6, the IFee = 10.5% of PBT

If PBT <=15E6, the IFee = 11.5% of PBT

If PBT <=20E6, the IFee = 13.5% of PBT

If PBT <=30E6, the IFee = 17.5% of PBT

If PBT <=50E6, the IFee = 20.0% of PBT

If PBT >50E6, the IFee = 25% of PBT


So how do I find the relevant applicable IFee % is say for instance the PBT is 12,500,000 which is >10E6 but < 15E6 and therefore the corresponding IFee % has to be a pro rata value, in this case 11%.

The numbers are large and random in value e.g PBT is 13,628,729. The corresponding IFee can be found mathematically but how do I find a formula in excel to find the same. Lookup always gives the lower number in a table, what about the next higher number.


Any input is much appreciated.


Regards


Rodrigo

 

[Hui]

Rodrigo

I have assumed you have a table of Values and Rates in A1:B8 like below

[pre]

____________A		 B
1	  -   		 10
2	  5,000,000 	10
3	 10,000,000 	10.5
4	 15,000,000 	11.5
5	 20,000,000 	13.5
6	 30,000,000 	17.5
7	 50,000,000 	20
8	 999,000,000 	25[/pre]
and the value you want to look up is in D1:

D1: 12,500,000


Then the following will return the pro-ratad rate


=TREND(OFFSET($B$1,MATCH($D$1,$A$2:$A$9,1),0,2),OFFSET($A$1,MATCH($D$1,$A$2:$A$9,1),0,2),D1)

Adjust ranges to suit


ps: No IF statements were used

 

[Rodrigo]

Hui, you are an awesome Excel Ninja. Thanks so much for your post, it sorted out my problem.


Regards


Rodrigo