Hey there Sign in to join this conversationNew here? Join for free

Excel: Finding x values from y values using a trendline

Announcements Posted on
TSR Movie Madness. Vote and debate in all of the group finals from now to Saturday evening! 15-07-2014
    • Thread Starter
    • 1 follower
    Offline

    ReputationRep:
    Hi, so I have a scatter graph with a line of best fit or "trendline" as its called in excel and what I'm trying to do is create lines from specific y values across to the "trendline" and then down to the x axis to find the x value corresponding to the y value... Is this even possible? If it is please could someone with more knowledge than I let me know how to do it as soon as possible! Thanks in advance!
    • 0 followers
    Offline

    ReputationRep:
    Choose to display the equation of the trendline and then do it with maths.
    • Thread Starter
    • 1 follower
    Offline

    ReputationRep:
    (Original post by majikthise)
    Choose to display the equation of the trendline and then do it with maths.
    Unfortunately I'm not that great at maths to be honest... The equation for the trendline is apparrently "y = 0.0312x + 0.0164" but that really dosent mean anything to me...

    I've come to realise that a paper graph may have been easier... I'll keep trying to do it in excel for now but If I can't do it by tomorrow evening I'll have to just go for the old fashioned method lol
    • 0 followers
    Offline

    ReputationRep:
    I think he was telling you how to find values from x or y values.

    AFAIK, there's no way Excel will draw them on for you. The best way is probably to play around with Autoshapes.
    • 1 follower
    Offline

    ReputationRep:
    (Original post by MALIK HAMID)
    Unfortunately I'm not that great at maths to be honest... The equation for the trendline is apparrently "y = 0.0312x + 0.0164" but that really dosent mean anything to me...

    I've come to realise that a paper graph may have been easier... I'll keep trying to do it in excel for now but If I can't do it by tomorrow evening I'll have to just go for the old fashioned method lol
    what that equation is saying is that for any given x value, you can calculate the y value by multiplying it by 0.0312 and adding 0.0164.

    So if you want to know what y is when x is 10, just do 0.0312*10 + 0.0164

    If you want to work out the x value for a given y instead, you just need to rearrange the equation to make x the subject, so for this it would be x = (y-0.0164)/0.0312


    I don't know what your graph looks like, but from the equation you've given, it sounds like the line of best fit should probably go through the origin. There is an option in the format trendline window called 'set intercept' which you can change it so that it does.
    • Thread Starter
    • 1 follower
    Offline

    ReputationRep:
    (Original post by alex-hs)
    what that equation is saying is that for any given x value, you can calculate the y value by multiplying it by 0.0312 and adding 0.0164.

    So if you want to know what y is when x is 10, just do 0.0312*10 + 0.0164

    If you want to work out the x value for a given y instead, you just need to rearrange the equation to make x the subject, so for this it would be x = (y-0.0164)/0.0312


    I don't know what your graph looks like, but from the equation you've given, it sounds like the line of best fit should probably go through the origin. There is an option in the format trendline window called 'set intercept' which you can change it so that it does.
    Thanks a lot this was really helpful!

Reply

Submit reply

Register

Thanks for posting! You just need to create an account in order to submit the post
  1. this can't be left blank
    that username has been taken, please choose another Forgotten your password?

    this is what you'll be called on TSR

  2. this can't be left blank
    this email is already registered. Forgotten your password?

    never shared and never spammed

  3. this can't be left blank

    6 characters or longer with both numbers and letters is safer

  4. this can't be left empty
    your full birthday is required
  1. By joining you agree to our Ts and Cs, privacy policy and site rules

  2. Slide the button to the right to create your account

    Slide to join now Processing…

Updated: November 10, 2010
New on TSR

Get ready for SQA results day

Share your grade expectations for Tuesday 5 August

Article updates
Reputation gems:
You get these gems as you gain rep from other members for making good contributions and giving helpful advice.