+ Reply to Thread
Results 1 to 2 of 2

Need help with multi-calculation function

  1. #1
    Registered User
    Join Date
    08-07-2009
    Location
    Florida, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Need help with multi-calculation function

    Hey guys, I'm trying to do something really basic for a spreadsheet I'm making for working with stocks. Basically it's aim is to calculate net profits/loss on a stock depending on different sell prices. Here's how it works:

    1) Number of shares = X
    2) Purchase price (cost per share) = Y
    3) Sell price (cost per share) = Z
    4) Sell price increment (ex: sell price of $10.00, increment of $0.10) = N
    5) Commission fee = {[(X*Y)+(Y*Z)]}*0.005+14

    The calculation should look something like this:

    X*Z - X*(Y+N) - commission fee = net profit/loss

    So here's what I need to do and what I'm having trouble accomplishing. I need the results to come out on multiple rows with each row incrementing by the price increment N. So if someone enters $0.10 then the first row displays the profit/loss *without* the increment, the second row would take into account N*1, the second N*2, the third N*3, etc.

    Here's the equation I have so far (as it appears in Excel), although it isn't working:

    PHP Code: 
    =(A2*C2)-(A2*B2)-(0.005*((A2*(C2+(D2*1))+(A2*B2))+14)) 
    A=number of shares, B=purchase price, C=sell price, D=price increment. E2 should display the value of C while E3 and beyond is the value of C plus D's incrementally increasing value.

    Help!

    Thanks everyone!

  2. #2
    Forum Contributor
    Join Date
    04-01-2009
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    280

    Re: Need help with multi-calculation function

    You can use ROW() to determine the row number you're on and use that as a multipler to increment.

    Sell price increment (ex: sell price of $10.00, increment of $0.10) = N
    So (N*ROW()-1) in row 2 would be the same as $0.10*1 and
    in row 3 it would be $0.10*2 and so on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1