# Adding a limit to a trendline

1. ## Adding a limit to a trendline

Hi all,

I am new on this forum so I apologize in advance if this is posted incorrectly or if the question has already been posted before. (in that case, I was not able to find it).
I am making graphs in excel which I know have a limit. I measure light in lumen and this value cannot be below 0, since that would be completely dark (it often doesn't even come close to it). On the y-axis I have the luminance in percentages, so 100% would be 0 lumen. On the x-axis I have the percentage of how much the quality of the contrastvalue of a screen improves. I want to add a trend line with a formula, but it should have the limit set on 100% (right now I've added a logarithmic trend line). I am not sure how to google this problem (I probably need to reread some of my math study books). I have tried it for a couple of days now but no luck. I registered here to hope someone can help me out. I have attached an image to make my problem more clear.

excel graph.PNG

2. ## Re: Adding a limit to a trendline

Show us your source data. Maybe we can figure something out. I have an idea that involves solver.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. ## Re: Adding a limit to a trendline

Rather than a logarithmic (or other built in trendline), I would probably lean towards a function that has an asymptote at 1. Something like a logistics function (https://en.wikipedia.org/wiki/Logistic_function ) which will require non-linear regression techniques (usually using Solver in Excel). Or maybe a hyperbolic function like y=1-A/x. Since neither of those trendline forms is built into the chart trendline tool, you will need to perform the regression in the spreadsheet.

4. ## Re: Adding a limit to a trendline

If you had some quantitative notion of the phenomenology of how luminance affects contrast (e.g., cross-screen reflections, integrating sphere, ...) it might provide some inside as to an appropriate regression model.

5. ## Re: Adding a limit to a trendline

Hi,

I've managed to recreate the results I would like. I hope what I did is sufficient. Ther is some extra information in the excel file

6. ## Re: Adding a limit to a trendline

Slightly different; see the Solver model.

7. ## Re: Adding a limit to a trendline

shg, I was able to "reverse engineer" your solver solution, but I can't see how you pulled it off without using a constraint.

The model I build was based on minimizing the sum of the squares of the differences, but I had to put a constraint that the maximum value was less than the specified value.

8. ## Re: Adding a limit to a trendline

but I had to put a constraint that the maximum value was less than the specified value
Don't understand ...

9. ## Re: Adding a limit to a trendline

One of the objectives is to limit the maximum to less than 100%.

I can now see that the formula is asymptotic and won't exceed 100%. I did not have that piece of information when I developed my model. I used a logarithmic model as the OP suggested.

Ah, gotcha.

11. ## Re: Adding a limit to a trendline

I think it would be more informative to plot and regress contrast (dependent variable) vs luminance (independent variable).

12. ## Re: Adding a limit to a trendline

Thanks everyone for the input and ideas. I have managed to acquire the information I needed by plotting the absolute values of the luminance and the contrast, this has a power trendline. I have learned about solver though so this thread has been very helpfull!

13. ## Re: Adding a limit to a trendline

The second tab in the worksheet regresses contrast as

contrast = a / luminance^2 + b / luminance

... to get an RSQ value of 0.98.

I'm not suggesting it's a better model, but it's a better fit.

There are currently 1 users browsing this thread. (0 members and 1 guests)