Please help I'am new to this........Thanks..
Posted by joel on February 03, 2001 10:01 PM
I want to automate my spredsheet for commissions,
example if loan $$ up to 500,000 * 10%
$$ up t 700,000 * 15%
$$ up to 1,000,000 * 20%
$$ up to 1,500,000 * 25%
so if loan amounts are less or equal to 500000 mulitply by 10% or if the loan amounts are > than 500,000 but <= to 699,999 mulitply by 15%, etc
How would I enter the formula? Please help!
Posted by Carl B on February 04, 2001 12:06 AM
This will post the Precentage
=If(Value(A1)>1000000,A1*25%, If(Value(A1)>700000,A1*20%, If(Value(A1)>500000,A1*15%,A1*10*)
This will post Value + Precentage
=If(Value(A1)>1000000,A1+A1*25%, If(Value(A1)>700000,A1+A1*20%, If(Value(A1)>500000,A1 + A1*15%,A1+A1*10*)
Posted by Aladin Akyurek on February 04, 2001 1:39 AM
Joel,
Type the following, say in column F (start in F1)
500000
700000
1000000
1500000
and in column G (start in G1)
10%
15%
20%
25%
Select the range F1:G4 and name it COMMISSIONS via the option Insert,Define,Name (or via the Name Box). When you do this, you can use COMMISSIONS in your formulas instead of $F$1:$G$4.
Now type in A2 a loan, say 697000 and enter in B2 the following formula:
=A2*VLOOKUP(A2,COMMISSIONS,2)
Aladin
Posted by Dave Hawley on February 04, 2001 5:36 PM
Hi Joel
Here is a custom function that will make life easier. To use it simply push Alt+F11, go to Insert>Module and paste in the code:
Function MyComm(Amount As Range)
Select Case Amount
Case Is <= 500000
MyComm = Amount * 0.1
Case Is <= 700000
MyComm = Amount * 0.15
Case Is <= 1000000
MyComm = Amount * 0.2
Case Is <= 1500000
MyComm = Amount * 0.25
End Select
Push Alt+Q to return to excel. Now save.
Now in any cell put:
=MyComm(A1)
Where A1 contains the amount.
Hope this helps
Dave
OzGrid Business Applications
Posted by Dave Hawley on February 04, 2001 8:14 PM
Push Alt+Q to return to excel. Now save.
OzGrid Business Applications
