Need help creating formula for given data

  • Thread starter Thread starter Need Assistance! Please Help!
  • Start date Start date
N

Need Assistance! Please Help!

I have develped a spreadsheet to assist me in my stock trading. I have one
column that I need to set up to calculate the commission rate. It took me a
couple of years to get the basic calculations information figured out back in
1998 and it has helped me a great deal when used on all my accounts. But, I
still lack the expertise in some calculation functions. I would greatly
appreciate anyones assistance. Given information follows:

Commision rate is equal to $7.00. If though by multiplying 5% of the total
price, that figure exceeds the $7.00 standard rate, then additional
commission is $0.005 per share that exceeds 1000 shares.
 
With total price in A2 and number of shares in B2

=IF(A2="","",7+(A2*5%>7)*MAX(0,B2-1000)*0.005)
 
One way
=MAX(7,7+IF(G3>1000,(G3-1000)*0.005))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"Need Assistance! Please Help!"
 
Unfortunately your formula did not work.

I'll should have been more specific. First, I am using MS Exel 2007. The
formula I need:

I have total shares listed in cell G2. The total cost of the purchase is in
cell V2. Now, the commission is standard at $7.00, but if I multiply total
cost by 5% and it exceeds $7.00, then commission is $7.00 plus $0.005 for
every share over the initial 1000 shares.
 
Unfortunately your formula did not work. I should have been more specific.
First, I am using MS Excel 2007. The Information follows:

I have total shares in cell G2. I have total cost of purchase in cell V2.
The standard commission is $7.00, but if I multiply the total cost of
purchase by 5% and it exceeds the $7.00, then the commission is $7.00 plus
$0.005 per share after the intial 1000 shares.
 
Try it this way where g2 is the purchase price and g3 is the number of shs.

=MAX(7,7+IF(AND(G2>140,G3>1000),(G3-1000)*0.005))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"Need Assistance! Please Help!"
 
Formula somewhat worked. Where did you come up with the 140 in the string?
 
How or where did you come up with the number 140 in this formula string? Just
curious for future reference.
 
"if I multiply the total cost of purchase by 5% and it exceeds the
$7.00"

is equivalent to

"if the total cost of purchase exceeds $140"

There you have the mysterious 140.

Lars-Åke
 
somewhat???
140 already explained

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"Need Assistance! Please Help!"
 
Back
Top