Announcement

Collapse
No announcement yet.

Market share formula

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    Re: Market share formula

    =Sales of product A/Total sales of all products in product class ABC

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          Re: Market share formula

          use this

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

          Comment


          • #6
            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!

            Comment


            • #7
              Re: Market share formula

              use this instead

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

              Comment


              • #8
                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!

                Comment


                • #9
                  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
                  Code:
                  =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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: Market share formula

                      this should do the trick...
                      Code:
                      =SUMPRODUCT(($A$2:$A$25=A2)*($B$2:$B$25=B2)*($C$2:$C$25=C2)*($D$2:$D$25))/
                      SUMPRODUCT(($A$2:$A$25=A2)*($C$2:$C$25=C2)*($D$2:$D$25))
                      Last edited by DaveR (UK); July 21st, 2006, 19:14.
                      D

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

                      Comment


                      • #12
                        Re: Market share formula

                        DaveR, thank you, you are the best!

                        Comment

                        Working...
                        X