Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Store commission Data (Win XP SP3 Excel XP)

    I have what has grown to be a very large table in Excel. The cells in column A contain the name of a customer. A sales representative name is in each of the next 65 columns in row 1 [heading row]. The remainder of the rows in these columns contain a commission percent. For example, Customer A, may be serviced by representatives Mary, Joe, Tom and Harry. Mary gets 50% of the commission, Joe 20% and Tom and Harry with 15% each.

    So if customer A is in Column A, row 2, the cells of row 2 that intersect with the columns for Mary, Joe, Tom and Harry containe the noted percents [they add to 100%]. The other cells in row 2 are blank for other representative columns. Given the growth of the company, what was once a managable table is now very large. This table is used as a "look-up table at month-end when calculating representative commission amount.

    This application should probably be in Access, but I know very little about Access and just about as little about Excel VBA, but my question is there a better way to utilize Excel other than using a table or grid for this information? The table is now 65 columns, with 1,450 rows. Of the cells in this range, only about 15% of them contain a percentage--the remaining 85% of cells in this range are blank.

    I am struggling to determine if Excel could store the commission percentage by customer, by service representative--without resorting to a "table or grid" arrangement that, because of growth, has so many blank cells. I still would need a way to "look-up" the commission percentages, by product, by service representative at month-end, when I drop the monthly sales information into another sheet to calculate the monthly commission amounts. Does anyone have a suggestion?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    You could create a database-like table with customers in column A, sales representatives in column B and commissions in column C.
    Only include the relevant customer-sales representative pairs in this table.

    You can then use SUMPRODUCT to look up the commission for a specific customer and sales representative:

    =SUMPRODUCT(($A$2:$A$1000="Company A")*($B$2:$B$1000="Tom")*$C$2:$C$1000)

    or, with the customer in H1 and the sales representative in H2:

    =SUMPRODUCT(($A$2:$A$1000=H1)*($B$2:$B$1000=H2)*$C $2:$C$1000)

    See attached example (I also added an alternative array formula)
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    Hans,
    As always I am thankful for your willingness to share your knowledge and talent with others...I will work to adopt this to my application. Wow, it is great!! Thank you.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    Hans,
    I am stuck again... Sorry I am with the "slow" Excel users crowd. I am trying to master the sumproduct formula. What I have added to your example is the actual sales in the cells in column d and in the cells in column e, I have multiple the percentage {column c} by the actual sales in column d. So far so good.

    Now what I am trying to do, is sum the total commission earned by person for the period [Mary, Joe, Tom and Harry]. I can't seem to get past the NA error message with my sumproduct formula. Can a sumproduct formula also sum the commission by person {column e} in my modified example, or am I totally misapplying the sumproduct formula and should use something else? Thanks for your patience.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    You can use SUMIF to add the total commission earned by a sales rep:

    =SUMIF(B2:B1000,"Tom",E2:E1000)

    or with the name in cell H2:

    =SUMIF(B2:B1000,H2,E2:E1000)

    Another possibility is to create a pivot table based on the data table with the sales rep in the row area and the commission earned in the data area.

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    Hans,
    Thanks. I will start with using the sumif formula to see if I can get that to work. I have not ventured into the land of Pivot Tables yet....So maybe its time.

    Assuming I master the sumif formula, I will have about 600 of these cells on 12 different sheets [one sheet for each month of the year]. From a processing speed perspective, would it be better to use the 600 or so sumif formulas on each of the 12 sheets [I need to keep this in one workbook if I can ] or use a Pivot Table on each sheet to derive the monthly commission by service representative? Again, thank you for your advice.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    I would put everything onto one sheet. If you add a new column for MONTH you can with AUTOFILTER essentially create a "month only sheet". Then you can create 1 pivot table and even use the month as a pagefield.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    Steve,
    Thanks for the insight. I have completed some additional research on the workbook. I will have almost 2000 rows with 3 columns for the commission data [customer, service representative, percentage]. Each month there are 6 data elements that need to be multiplied by the percentage. In certain months, a customer may not have sales, etc. activity.

    Here is what I was thinking...To keep the master commission schedule on a single worksheet. Then have 12 other sheets--one for each month. I would then populate that month's six data elements [sales, sales returns, accounts receivable, etc.] in the worksheet for that month [the data is copied from another software application--two columns of data, customer and amount for each of the six elements [sales, discounts, etc.]. I will then need a lookup function using the current month activity customer to secure the % by matching the customer number to the master commision schedule. Given the way that I must import the current month data [i.e. there are twelve columns, two columns for each series---customer and amount], this look-up process will need to be done six times [once for each set of columns].

    The next step is to multiple the current month amount by the percentage. The final step is then to sum by service representative the commission earned for the month. Does this approach make sense or is there a better way to handle this data and the desired result? THANKS.

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    Steve,
    There is an error in my logic in the previous post...I am going to have to process the look-up function from the master list and "look-up" the current month amount instead of using the current month data to look up the percentage in the master commission sheet. This is because there is a many-to-one relationship in the master commission list [i.e. there are multiple commission percentages for the same customer].

    When I tried what I said in the previous post, using the current month data to lookup the commission percentage---only the first representative percentage listed for the customer was returned...not anyone else. So if I switch the look up logic to use the master commission table to identify the current month number it seems to function properly...though in the first month I am using to program the January sheet about 2/3 of the cells in the commission column are empty----meaning that there was no activity for that customer in the month of January.

    I had hoped to avoid "empty" cells, by using the current month data for the look-up [i.e., in Han's example, when I looked up Acme's percentages using the current month sales amount, only Mary received the commission calculation as the next customer was Billing&Co [Joe, Tom and Harry were missed]. If I am to use the current month data for the look-up and eliminate the blank cell calculations, then I need logic to return all of the representatives and their respective commission amounts for a particular customer when the lookup function for the current month customer matches a customer in the master commission sheet---and it could be as few as two who share the commission to as many as 5 individuals [ i.e., there is no limit imposed by the business for the number of individuals sharing a customer commission---5 is just the current "high" number of representatives sharing an account].

    I hope that this makes sense.....I am doing my best to explain it. Thanks for your patience.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    Could you attach an example of what the data actually look like? I've lost track.

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    Hans,
    I have added a sheet to your example. As you can see, I am using the commission grid to "lookup" the current month activity. This results in blank cells when there is no current month customer activity. When carried through 2000 rows...there are many blank cells.

    But if I reverse the logic and lookup using the actual monthly data, I can't figure out to insert rows and capture the other people that share in the account [i.e. there is a many-to-one relationship between representatives and a customer account]. There is probably a more glamourous solution, but it is probably beyond my meager skills. Let me know if you have any ideas. THANKS.
    Attached Files Attached Files

  12. #12
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    Hans,
    One other thing I forgot to mention...and its important...Is that I still need to sum by representative each column. For example, in the Oct Sales column, when all the lookups have been processed, I need as the "final" output, a sum of the Oct sales column, by sales representative {Tom, Mary, Joe, etc.}. I would need this same information for each of the other five columns....I haven't figured out how to do this yet. THANKS.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    You can use a pivot table with the sales rep in the row area and the 6 data items in the data area to total the amount by sales rep.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    I would much prefer to use an Access database, but I don't know the format of the data to be imported, so I can't tell how easy or hard it would be to get the data into Access.

  15. #15
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Store commission Data (Win XP SP3 Excel XP)

    Hans,
    The data being imported is a 2 column CSV file...there are six files to import. I am even weaker in my knowledge of Access...so I am probably stuck with an EXCEL application for now.

    One question, related to EXCEL memory usage. I was thinking about making the commission calculation worksheets [1 master worksheet and 12 monthly worksheets ] a separate workbook and only reference the final totals for each representative from this workbook to the workbook that would have a separate sheet for each representative [55 sheets]. By "spliting" this application into two workbooks, I would assume that less memory is consumed, correct? Even if there is no memory advantage, would you split this application into two workbooks anyway? If not, then I may as well leave it in its current single workbook design [68 sheets] and just update sheets per your initial example.

Page 1 of 2 12 LastLast

Posting Permissions

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