I'm asking for help in calculating deciles values for certain columns in my attached spread sheet. I have a inserted comment along side each column that I need help with. Would you please take a look and give me your feedback. Thank you.
I'm asking for help in calculating deciles values for certain columns in my attached spread sheet. I have a inserted comment along side each column that I need help with. Would you please take a look and give me your feedback. Thank you.
Last edited by nander; 10-29-2010 at 10:22 AM.
I'm not sure what you're asking, because one of them is a simple formula and the others are typed-in constants. But here are the answers to your questions:
How are the Decile Values calculated in Column A? The numbers in Column A are the subtotals of all values up to each point divided by the total of all values. (Editorial: This formula is rather brute force and I would do it differently. But it works.)
How are the Decile Values calculated in Column F? They are not calculated at all. They are arbitrary numbers that have been typed in. They probably represent business objectives. I could add more but I'm not sure what they mean by "pricing action."
How are the values calculated in Column E? Same as Column F question.
How are the Incremental Margins calculated in column F? These numbers are also typed in. I see no straightforward way that they may have been calculated.
Thank you for your feedback. How would you calculate the values in column A vs my brute force method. I saw an example of using the PERCENTILE function. but I'm not familiar with how to use it. Even though the other columns have values key vs calculated I used this as a way of showing the results that I was trying to achieve. I've attached an article where I've tried to replicate the spreadsheet
Last edited by nander; 10-29-2010 at 10:23 AM.
To simplify the formulas in column A, put the following formula in A7
=SUM(C$6:C7)/$C$19
then copy it to cells A8:A16. However, based on a larger look at what you are trying to do, I'm not sure it even makes sense. Normally, determination of deciles (or quartiles, or percentiles) is a statistical analysis step, determining where to put the breakpoints in the data to break the population into equal segments. In your case, however, you are simply calculating the percentage points, which just verifies that they are 10, 20, 30, etc. It doesn't seem to give you any information. The hard part is taking the raw sales data then coming up with the figures used in column C, which is already done.
As for your other figures, trying to determine what formulas to use based on the document you attached is not really Excel help. I think you need help understanding your course material better before we can start talking about how to implement it in Excel. I'm afraid I am not able to study the paper you attached at the moment to sort it out; perhaps someone else with a background specific to this area can help.
I updated the formula you provided. I've contacted the authur of the article and perhaps he will explain. Thank you for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks