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.
Thanks in advance!

excel graph.PNG

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.

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.

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.

Slightly different; see the Solver model.

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.

but I had to put a constraint that the maximum value was less than the specified value

Don't understand ...

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.

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

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.