+ Reply to Thread
Results 1 to 13 of 13

Adding a limit to a trendline

  1. #1
    Registered User
    Join Date
    03-27-2019
    Location
    Rotterdam, Netherlands
    MS-Off Ver
    2016
    Posts
    3

    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
    Last edited by MartyMcFly85; 03-28-2019 at 04:50 AM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    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.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,897

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    03-27-2019
    Location
    Rotterdam, Netherlands
    MS-Off Ver
    2016
    Posts
    3

    Re: Adding a limit to a trendline

    Hi,

    Thanks for your reply.

    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

    Thanks in advance
    Attached Files Attached Files

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Adding a limit to a trendline

    Slightly different; see the Solver model.
    Attached Files Attached Files

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    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. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    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.

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Adding a limit to a trendline

    Ah, gotcha.

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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. #12
    Registered User
    Join Date
    03-27-2019
    Location
    Rotterdam, Netherlands
    MS-Off Ver
    2016
    Posts
    3

    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. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adding trendline for certain values
    By Texasandreas in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-28-2016, 06:37 AM
  2. Replies: 6
    Last Post: 10-16-2014, 08:44 PM
  3. Replies: 0
    Last Post: 10-11-2011, 03:22 PM
  4. Adding a trendline to chart
    By controlfreak in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-23-2009, 05:49 AM
  5. Adding TrendLine to my chart
    By walid66 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-06-2008, 10:00 AM
  6. Adding Trendline To Just Some of the Bars in Chart
    By Carroll in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-27-2006, 09:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1