Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Market share formula

  1. #1
    Join Date
    30th June 2006
    Location
    Netherlands
    Posts
    68

    Market share formula

    Hi,
    How can i formulate the following in a formula:
    Sales of product A (product A belongs to product class ABC) divided by the total sales of all products in product class ABC
    Thanks in advance!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

  3. #3
    Join Date
    26th July 2004
    Posts
    1,293

    Re: Market share formula

    use the SUMPRODUCT function

    =SUMPRODUCT(((A7:A13)="A")*(B7:B13))/SUM(B7:B13)

    where Product names are in Col A and sales in Col B

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    30th June 2006
    Location
    Netherlands
    Posts
    68

    Re: Market share formula

    Thanks Dave!
    I don't know how the calculate the total sales for a product class. Let me attach an little file. This formula has to be valid for every record.
    Thanks again.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th July 2004
    Posts
    1,293

    Re: Market share formula

    use this

    =SUMPRODUCT(((A2:A7)="A")*((B2:B7)="abc")*(C2:C7))/SUM(C2:C7)

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    30th June 2006
    Location
    Netherlands
    Posts
    68

    Re: Market share formula

    Thank you Pangolin for your answers.
    This formula is certainly one in the good direction but if you watch ht efile closely you will find out that product a has a market share of 10/(10+34) of the total abc market. That let's say the market share of product a in the abc market is around 22%. When i execute your formula i only got 4%. So something is missing in this formula.
    Thanks in advance!

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th July 2004
    Posts
    1,293

    Re: Market share formula

    use this instead

    =SUMPRODUCT(((A2:A7)="A")*((B2:B7)="abc")*(C2:C7))/SUMPRODUCT(((B2:B7)="abc")*(C2:C7))

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    30th June 2006
    Location
    Netherlands
    Posts
    68

    Re: Market share formula

    Thanks again!

    The formula works wonderful! But it is only valid for product A and product class abc. Is there a way to copy this formula down to all records. My file contains thousands of products and hundreds of product classes and i don't want to rewrite this formula for every record.

    My question is to find a formula that calculates the share of a product's sale to the total sales of that product class.

    I hope someone knows!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748

    Re: Market share formula

    In the formula you have already got, you just need to substitute 'A' for the required product and 'ABC' for the product class required...alternatively you can have these values in a cell so
    if in D1 you have the product and D2 you have the product class, the formula would change to
    VB:
    =SUMPRODUCT(((A2:A7)=D1)*((B2:B7)=D2)*(C2:C7))/SUMPRODUCT(((B2:B7)=D2)*(C2:C7)) 
    
    
    Hope this helps...
    D

    Better a bad day on the water than a good day in the office

  10. #10
    Join Date
    30th June 2006
    Location
    Netherlands
    Posts
    68

    Re: Market share formula

    Hi,
    Thanks DaveR! Your solution worked perfectly. Now i want to extend this formula with another variable: period. Now i want to calculate the share of a product'sales to the total sales of the product class sales in a particular period. I put the period variable in Column A and modified the formula in the following way:

    =SUMPRODUCT(((A$2:A$25)=A2)*(B$2:B$25)=B2)*((C$2:C$25)=C2)*(D$2:D$25))/SUMPRODUCT(((C$2:C$25)=C2)*(D$2:D$25))

    Unfortunately this doesn't work. I can't understand why it doesn't work.
    Thanks in advance!
    Kees
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Calculating average market share from a pivot table
    By keesberbee in forum EXCEL HELP
    Replies: 5
    Last Post: August 5th, 2010, 14:16
  2. Formula Error For Tax Amortization/fair Market Value
    By ejmontoya in forum EXCEL HELP
    Replies: 2
    Last Post: January 17th, 2007, 15:56
  3. Have to show market growth and share
    By keesberbee in forum EXCEL HELP
    Replies: 6
    Last Post: July 13th, 2006, 22:17

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
  •  
porno