Regression data

[marco.rota]

Hello to everyone.

I'm facing an issue on Excel chart. I cannot find the correct trendline for this chart.

My goal is: find a function that give me a markup for a specific price.

The function has to be a trendline of different price and markup as per attached file.

Can anyone help me on this matter? Also explain why Excel is not making the right trendline?

Thanks
Marco

[MrShorty]

I'm not sure I understand your question. It appears to me that none of the built in chart trendline equation forms is suitable for your data. You can program the spreadsheet to regress the data against just about any function you can come up with. What regression equation do you want to use for this?

[marco.rota]

Thanks for your reply.

I would like to use any function that explain the majority of the data.

I have tried the Power trendline (in red) but it doesn't match my guessing.

I would like to have a function more similar to the green line.

Attachment 631004

Sorry for my bad english. Hope it's more clear now. Any other question, let me know.

Marco

[shg]

That data is unlike any price markup data I've seen.

[MrShorty]

Your attachment failed to attach, so I cannot see the red and green lines you mention.

So you have not yet chosen a regression equation. We are usually pretty good at Excel questions (how to perform a regression against a chosen function in Excel), but don't always have the expertise to tackle non-Excel questions (like choosing a regression equation). I don't think any of us here has the expertise to recommend a regression equation based only on the raw data presented. Can you tell us anything about the desired regression equation?

One observation, if I format your vertical axis to be a logarithmic axis (so I create a log-log plot where a power trendline should look like a straight line), it appears to me that the raw data has some artificial boundaries or something applied to it that form two straight lines on the log-log plot. I might look at whatever is causing those artificial boundaries and see what that might suggest for a regression equation.

[shg]

Your second attachment can be opened from Chrome, and I've attached it here.

You've used piecewise power series regressions, and the r^2 values are terrible for all but the last. If you change the plot to log-log, you'll also see that they are not piece-wise continuous, and why no smooth function could be applied to all the data.

[marco.rota]

Thanks for all your reply.

I'd try to explain what the Excel file contains.

There are the prices on the horizontal axis and there are the mark-ups in the vertical axis.

I put all the data in a scatter chart. The horizonal axis was changed in a logarithmic scale in order to have a better view.

Selecting a datum on the graph, I inserted a trendline of POWER type (in red) but I would like to find a function more similar to the green one (I have drawn manually the curve).

Chart.PNG

For example, on the other sheet, I divided the data by ranges to see if I could created a more accurate curve.

Chart_2.PNG

Only now I wanted to find a curve that included them all but I'm not able to create the right function.

Hopw it's more understandable.

Marco

[MrShorty]

I think I understand what you are trying to do, but I have no idea what kind of regression equation to choose. It often seems that the most difficult part of these questions is the non-Excel question of which regression equation to choose. I have no idea what kind of regression equation would be appropriate.

As a purely empirical observation (so it has no basis in reality): If I make both axes linear, I can talk myself into seeing a hyperbola/reciprocal function in the data.
https://en.wikipedia.org/wiki/Hyperb...uation_y_=_A/x
https://calculus.subwiki.org/wiki/Reciprocal_function
https://www.mathsisfun.com/sets/func...eciprocal.html

I could see a regression equation like y=m*(1/x)+b possibly providing a decent fit of the data. I have no idea what kind of price/markup dynamics would be described by such an equation.

[marco.rota]

Thanks for your replies and advices.

What kind of regression do you think it could be applied?

Marco

[shg]

What does that data actually represent?

[marco.rota]

At the moment, same prices are multiplied with different markups.

The data in the table represents the prices with their relative markups.

For example:

Price: 1,000000 - Markup: 3,410000
Price: 1,000000 - Markup: 2,500000
Price: 1,000000 - Markup: 2,610000
Price: 1,000000 - Markup: 2,500000

What I would like to have is: a function that can represent at the best the data and consequently when I have a price I can calculate the unique mark-up.

[shg]

What kind of markup scheme adds 500 to a price of 0.002 and 2.9 to a price of 6534?

[marco.rota]

Unfortunately these are the data that I have.

[MrShorty]

I can't speak to what kind of markup scheme this is, I will note (following up on my reciprocal function idea) that, if you take the reciprocal of price, a high percentage (~60%) of those points where price is less than about 0.4, the markup is exactly the reciprocal of price. 500=1/0.002 (the very first data point), 2.5=1/0.4, 111.11=1/0.009, 50=1/0.02, 10=1/0.1, and so on.

I also note that there seems to be some kind of "break" in the data at price=0.4. Below price=0.4, there seems to be one way to calculate markup, and above price=0.4, there is a different way to calculate markup.

Beyond that, it makes no sense to me.

[marco.rota]

Thanks again for you explanation and help.

I'll try to apply some of your tips and make some changes.
I'll see if I can get some better curves.

Marco