Results 1 to 2 of 2
  1. #1
    Eve B

    Function/Macro Needed?

    This may be difficult to explain, so please bear with me.

    I have to subtotal # of shares for each employee by their option strike price. We have the data so each employee has one line and the shares information goes across the row. For example:

    EE Name Initial Grant Performance Grant Promotion Grant
    John Doe 1,000 @ $5 500 @ $3 500 @ $3
    (These are totally arbitrary numbers!!)

    So I'd like to add another column for # of $5 options and another column for # of $3 options. So it would read like:

    # of $5 # of $3
    1,000 1,000

    The problem I have is that we have up to 6 columns of options at this point (see attached for example) and I can't figure out how to do a formula (or macro) to do what I look at the strike price, determine if it is the amount I am looking for, if it is then to remember that amount, look at the next price, if it is the amount I want then to add it to the previous #, if it is not then to go on to the next price and so on.

    [I do have the information by grant (so there are multiple lines per person....each grant's info per line), and I could subtotal that by person, however that makes it difficult to pull that information into other reports we're doing.]

    Any ideas???

    Thank you!!!

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    Thanked 6 Times in 6 Posts

    Re: Function/Macro Needed?

    Eve, it's difficult to know exactly what you are looking to sum; if you want a total of all options which are "above water", that is all options granted below the current trading price, you could do this with a series of =sumif() formulae, but you'll have to add new =sumif()'s every time a new grant is issued, to address the new columns. For what you attached, if AM1 is the current trade price, you can copy this formula into cell AM3:

    =SUMIF(H3,">"&$AM$1,G3)+SUMIF(P3,">"&$AM$1,O3)+SUM IF(X3,">"&$AM$1,W3)+SUMIF(AF3,">"&$AM$1,AE3)

    Then copy it down for the other employees.

    (I have a strong bias towards simple solutions; there may be better ways, but one day you may be handing this off to someone else.)

    I'd recommend you change the data layout, because I assume that each employee can get grants for new hire, promotion, performance, and that some of these will happen every year or more often. In fact if you have more than 40 - 50 employees, I'd move to Access to track the data, where the employee data is the primary table and each grant becomes a subsidiary table.

    If you decide to manage in Excel, I'd change the layout to one sheet per employee, where the grants go down the page, and the grant reason, which is now a heading, becomes a field. Then set up a summary sheet where you can address each employee's data as a database, and perform all-employee analysis. (I'm only one employee, but this is how I track my grants in Excel)
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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