I have an interesting math exercise I'm trying to do in Excel that I need some help with.
I have a datasets for a group of product's lifecycle. I've normalized that data set and calculated the balance of sale for each period, as below:
Here's how that curve looks in a chart:
Now however the challenge is to determine what percentage of the product would sell each month if the lifecycle was stretched to 14 months, 18 months, or further, were the product to have a longer run. I need to:
* Stretch or compress the scale as appropriate. (So, for instance, if we scale to 14 months, I'll need to add two months, inserted between 4 & 5 and 8 & 9.)
* Interpolate the data points that will be added.
Here's what that should look like. The new data points and their interpolated values (just a linear average of the adjacent values) are highlighted in green.
I'm struggling with finding a good way to have Excel look at the new data points on the scale (let's call them 4.5 and 8.5) and determine that it needs to do the calculation to interpolate that datapoint, and if it's not a new data point, to get the relevant data from the original table. This gets confusing because the new points (4.5 and 8.5) are not really named that way; they're numeric, in sequence. So 4.5 is really 5, old 5 is really 6, old 6 is really 7, old 7 is really 8, the new 8.5 is really 9, and that means old 8 is now 10, etc. It's confusing, and I just can't figure out how to build that formula for Excel.
I'd apprecite any suggestions on how to approach this.
Thanks!
I have a datasets for a group of product's lifecycle. I've normalized that data set and calculated the balance of sale for each period, as below:
| Month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| Amount Sold | 50 | 60 | 100 | 120 | 150 | 145 | 118 | 122 | 105 | 80 | 65 | 40 |
| % to Total | 4.3% | 5.2% | 8.7% | 10.4% | 13.0% | 12.6% | 10.2% | 10.6% | 9.1% | 6.9% | 5.6% | 3.5% |
| Normalized | 9% | 18% | 55% | 73% | 100% | 95% | 71% | 75% | 59% | 36% | 23% | 0% |
Here's how that curve looks in a chart:
Now however the challenge is to determine what percentage of the product would sell each month if the lifecycle was stretched to 14 months, 18 months, or further, were the product to have a longer run. I need to:
* Stretch or compress the scale as appropriate. (So, for instance, if we scale to 14 months, I'll need to add two months, inserted between 4 & 5 and 8 & 9.)
* Interpolate the data points that will be added.
Here's what that should look like. The new data points and their interpolated values (just a linear average of the adjacent values) are highlighted in green.
I'm struggling with finding a good way to have Excel look at the new data points on the scale (let's call them 4.5 and 8.5) and determine that it needs to do the calculation to interpolate that datapoint, and if it's not a new data point, to get the relevant data from the original table. This gets confusing because the new points (4.5 and 8.5) are not really named that way; they're numeric, in sequence. So 4.5 is really 5, old 5 is really 6, old 6 is really 7, old 7 is really 8, the new 8.5 is really 9, and that means old 8 is now 10, etc. It's confusing, and I just can't figure out how to build that formula for Excel.
I'd apprecite any suggestions on how to approach this.
Thanks!
Last edited:
