rudebwoyrohan
New Member
- Joined
- Jul 4, 2015
- Messages
- 2
I have a rate in G9 on TAB 1. I have ranges on TAB 2. This is what I need: If this rate is within the min (A2:A14) and max (B2:B14) of the values in the table on TAB 2 then choose the amount of the corresponding fee in [FONT=inherit !important][FONT=inherit !important]column C[/FONT][/FONT] (C2:C14).
VLOOKUP would be perfect only that the output of the lowest and the highest range is not a set number but a percentage. If G9 is equal or above A2 then the output is 15% of G9 but capped at 0.05 and if G9 is betw A14 and B14 then the output is 15% of G9.
PLEASE HELP!!! Thanks in advance guys!
<tbody>
</tbody>
<tbody>
</tbody>
VLOOKUP would be perfect only that the output of the lowest and the highest range is not a set number but a percentage. If G9 is equal or above A2 then the output is 15% of G9 but capped at 0.05 and if G9 is betw A14 and B14 then the output is 15% of G9.
PLEASE HELP!!! Thanks in advance guys!
G | |
RATE | |
9 | 15 |
<tbody>
</tbody>
A | B | C | |
1 | MIN | MAX | FEE |
2 | 25 | INFINITY | 15% CAPPED AT 0.05 |
3 | 23 | 24 | 0.018 |
4 | 21 | 22 | 0.016 |
5 | 19 | 20 | 0.014 |
6 | 17 | 18 | 0.012 |
7 | 15 | 16 | 0.01 |
8 | 13 | 14 | 0.008 |
9 | 11 | 12 | 0.006 |
10 | 9 | 10 | 0.005 |
11 | 7 | 8 | 0.004 |
12 | 5 | 6 | 0.003 |
13 | 3 | 4 | 0.002 |
14 | 1 | 2 | 15% |
<tbody>
</tbody>