0

First I want to introduce myself I am not good at math. Please enlighten me if this is kind of crazy idea.

enter image description here

So in the example screenshot, you can see I am computing for the sales growth.

Also you can see there is a grand total, the one inside the red box.

As you can see the value in the red box is the sum of all the values under each total.

Now, I am getting the results that I want. Except for the sales growth. As you can see the sales growth is correct for each row based on the formula given.

Now, the problem arise in the sales growth total. It just sum up all the sales growth value. Which is wrong. Because it doesn't follow the formula. It just add up the values.

Now my question is can I get the the correct answer which is $2.4060$ based on the values inside the green box? Can I achieve this by not following the formula? but still achieve the same answer?

I mean let's say it add all the value. So we get, $1.91$. But is there additional way or technique that I can perform to achieve the answer which is $2.4060$?

I am thinking that there is a clever way to do this. Something that I cannot think because I am not good at numbers.

I hope I explained myself clearly and you understand what I am trying to do.

Any suggestion would be appreciated so much.

Thanks,

Dan

Dan Angelo Alcanar
  • 103

1 Answers1

0

The spreadsheet is wrong when it computes sales growth. The formula you give is correct. The CREAM sales growth should be $-16.667\%$, not some tiny number. ASSORT and BROWN will have problems with the formula because there were none sold last year, maybe because they are new introductions. People often put in $+100\%$ for this, but it should be infinite.

When you sum a number of products the sales growth of the total will not be the total of the sales growths of each product. A simple way to see this is to imagine five products that each grow $10\%$. The sum of the five will also grow $10\%$, not $50\%$. Your 1104 line looks like it might be intended to be the sum of the lines below, though the numbers don't work out. Maybe there are lines below that make up the rest. The sales growth for 1104 should be computed using the data on that line, giving $2.40606$ as you say, which should be represented as $240.606\%$

Ross Millikan
  • 374,822
  • Hi @Ross thanks for clearing things up. I am still trying to understand what you said. But you said that CREAM should be -16.667%, may I ask how you get that result? Is it because you multiply it by 100? – Dan Angelo Alcanar Mar 04 '18 at 14:52
  • Hi @Ross yes you are right there are more lines below I just the first group. So there is no way I can get the 240.606% based on the summation of the sales growth of each products? :( Thank you. – Dan Angelo Alcanar Mar 04 '18 at 15:02
  • Yes, percent means hundredths, so if something decreases by $\frac 15$ it is $-20%$. You could also write that as $-0.2$ times. Excel has a formatting option to do this. No, you can't get the growth percentage of a sum from the sum of the growth percentages of the items. If one item has much larger quantity than the others its growth percentage will dominate over the others. You could get it as a weighted average, but that is a lot of trouble. – Ross Millikan Mar 04 '18 at 15:18
  • Hi @Ross. Thank you for your guidance and clear explanation. Actually it is a pivot table in a webpage. I am using a library. But I think I have to create my own. Thankyou again. Cheers – Dan Angelo Alcanar Mar 04 '18 at 16:02