Adding +Value to a Spreadsheet

[OlYeller21]

I have a list of items. Each one has variations that make the "next higher level" cost slightly more. The increase between them is rarely the same and I need to see some statistics on those numbers.

I'd like paste a formula in a column next to the column of prices.

I can sort the prices by item then price again - so the sequence of prices would decrease as opposed to increase.

The only thing I can think of is to create a formula that finds the difference of a number and the number above it while ignoring non-positive numbers. The only problem I can see is if the last price of a product (the highest number) is less than the cheapest version of the next item. I don't know if there's a way to handle that - each product can have a variable amount of options so I can't make a forumla or set of formulas care about how many steps up its taken already.

Here are some numbers in the example:

682
727
824
869
914
959
1004
1049
1094
682
727
824
869
914
959
1004
1049
1094
339
355
401
417
433
449
465
481
497.........

Any help you can provide would be greatly appreciated.

[AliGW]

Too little data! Just a list of numbers is in no way representative of what you are describing.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[MarvinP]

Hi OlYeller,

The first difference between numbers seems constant, implying a steady increase in velocity. See if this attached helps you decide what to do with your numbers. It looks like the first difference is a little more than twice the other differences. Does this help?

First Differential.xlsx

[OlYeller21]

Thanks!

I'm looking at the values in Column I (Plus Values). You can see that a simple difference formula doesn't work in row 16.

[MarvinP]

Hey olYeller,

I'm clueless on what answer you are looking for. Do you want a formula? I think you want to analyze your data but I don't know what criteria you are using. I'm afraid we can't be much help without more info or direction. Normally with differences you are looking for a polynomial graph, and in your case it would have a maximum power of 2. Does that make any sense or fit into your problem?

[OlYeller21]

Ultimately, I want to look at statistics for the differences between a product and the prices for the next level up from it.

So if Product A with Option 1 costs $10 and Product A with Option 2 costs, $20, the difference is $10. On a global level (several million data points), I want to look at some statistics of that difference (only the "up" cost, right now).

If a company spends a large amount of time determining the difference in cost between two products, the difference between that method and just assigning a general +value may save a significant amount of money. So the question is, "What costs less? Ensuring the +Value is accurately based on actual costs. Or assigning a generic +Value to products and taking a hit based on some level of error?" First thing I'll need is what every +Value currently is.

So I was able to get hundreds of thousands of data points. They're currently sorted by product name, which includes the options available (mostly decisions with 5-10 options each). I have the total price of each product (Column J) and I want to paste a formula all the way down column I that shows the +Value of every item's more expensive option.

So your formula did exactly what I wanted it to, except when a new product starts and happens to cost more than the most expensive option of the product before it. See I16 for the example.

I'm thinking of two options:
*One that checks the product's template ID (the same for all product options of one product) and proceed with calculating if they're the same.
*Look for a large jump in all +Values where I could draw a line by nesting an IF statement. In other words, if all of the correct +Values are no larger than 114, then an IF statement changes any +Value over 114 to zero.

The second option is still a maybe. I could probably deal with some level of error. But I'd rather have correct census data if possible.

As a note, this is an idea for a project I'll eventually be doing for an MBA course. I'm at the proof-of-concept stage at this point.

[MarvinP]

Two thoughts,

Sort all your data by price first (small to large) and then Product Name. This should arrange your data correctly to do the problem. Then I'm thinking a Percent Change would be more helpful than total dollar change.
Somewhere you should also list (in a different column) the upgraded product info. So base model might cost 1000 and with better color/paint adds 5% and with better tires/rims adds 10%. You'll also need to figure how to arrange stuff when multiple selections of a product don't have the lower cost features included.

[OlYeller21]

So if I sort by Product Template External ID (the UUID per product) then sort by price, I'll have a band of prices. Each each price band, I paste in a formula to find the same Product Template ID in the first band, then give the difference in price between the first price and the current band's price. If it's not found, price difference is zero.

I think that would work, right?

If all look back to the first band of prices (lowest price per product template), I'll get the total difference between the lowest price and that product variant's price. I could then find the differences between those in a couple different ways.

Sounds right to you?