lookup and prorata calculations

Stefyu89

New Member
Joined
Jan 6, 2013
Messages
8
i've a question concerning a lookup on a list of rate of return for this 3 hurdles level of earnings. You must calculate the proper rate of return for different rate and use prorata for earnings between 2 hurdles rate. Hope i'm clear ( French speaking guy!!! my grammar is not so perfect !!! i'm working on it.).

following a visual description of what i'm stuck with.

Sure it's a classic but can't answer.

Best regards

Table to look into :

<=10
20
>=60
15%
20%
35%

<tbody>
</tbody>

Different levels of earnings
January
February
March
April
25

9
65
14

<tbody>
</tbody>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Going off your example you could use an if statement =IF(A2<=10,15%,IF(A2=20,20%,IF(A2>=60,35%)))

Im Guessing you would want your table to be <=10, <=59 and >=60 though which would mean your statment would be :

=IF(A2<=10,15%,IF(A2<=59,20%,35%))
 
Upvote 0
Going off your example you could use an if statement =IF(A2<=10,15%,IF(A2=20,20%,IF(A2>=60,35%)))

Im Guessing you would want your table to be <=10, <=59 and >=60 though which would mean your statment would be :

=IF(A2<=10,15%,IF(A2<=59,20%,35%))

Thanks for your quick answer but not what i'm looking for.

As an illustration, if in January you got 14 of revenue : you must get 15% for the first 10th of revenue plus a prorata of between 11-20 for the 4th remaining at 20%.

Looking for an efficient way to compute this without infinite nested if statements.
Once more time thanks for answering quickly

Best regards,
 
Upvote 0
Hi there,

With my current knowledge of excel as i understand it when you have a set of logical tests to complete your calculation If statements are the easiest way to perform the logical test and complete a calculation when the condition is met. Is there a reason your needing to limit the number of nested ifs?

I am hoping and looking forward to someone posting solution that limits the use of if statements.

based on what you have described and desired outcome this is all i could think of:

I had the hurdles 10 20 60 in c2:e2

and the values of your sample data in c5:e5

Code:
=IF(C6<=$C$2,C6*$C$3,IF(AND(C6>$C$2,C6<=$D$2),-(((C6-$C$2)-C6)*$C$3)+((C6-$C$2)*$D$3),IF(C6<$E$2,C6*$D$3,C6*$E$3)))

I dont know how to do it any other way so i am looking forward to see what this board has to offer you

good luck


<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top