Finding a Point in an Excel Line Graph
I have this graph here:
I need to know what the y value is when x equals 5.5, however I do not know how to do that and adding a trendline is not very accurate for this graph, especially because it must be as accurate and precise as possible. Please help!
microsoft-excel charts
edited Feb 13 at 22:46
MarianD
1,4911718
asked Feb 13 at 22:17
Olivia Di BiaseOlivia Di Biase
1
add a comment |
I have this graph here:
I need to know what the y value is when x equals 5.5, however I do not know how to do that and adding a trendline is not very accurate for this graph, especially because it must be as accurate and precise as possible. Please help!
microsoft-excel charts
edited Feb 13 at 22:46
MarianD
1,4911718
asked Feb 13 at 22:17
Olivia Di BiaseOlivia Di Biase
1
1
how is the chart created? What is the data source?
– teylyn
Feb 13 at 22:46
I don't think you can get Y value based on X, it's math. You should provide the data source and based on the data source to calculate the result.
– Lee
Feb 14 at 1:17
When you have a lot of points and short segments between them, like this, each line segment can be approximated with a straight line and be reasonably close. That's what Jon Peltier's answer does. If you want more accuracy, you could take a uniform section of the data containing no inflection points (maybe x+3 thru x=9), and fit a 2nd order polynomial trendline. That is likely to be a very good fit for that section. Use the resulting formula to estimate the Y value more accurately.
– fixer1234
Feb 14 at 7:12
add a comment |
I have this graph here:
I need to know what the y value is when x equals 5.5, however I do not know how to do that and adding a trendline is not very accurate for this graph, especially because it must be as accurate and precise as possible. Please help!
microsoft-excel charts
edited Feb 13 at 22:46
MarianD
1,4911718
asked Feb 13 at 22:17
Olivia Di BiaseOlivia Di Biase
1
I have this graph here:
I need to know what the y value is when x equals 5.5, however I do not know how to do that and adding a trendline is not very accurate for this graph, especially because it must be as accurate and precise as possible. Please help!
microsoft-excel charts
microsoft-excel charts
edited Feb 13 at 22:46
MarianD
1,4911718
asked Feb 13 at 22:17
Olivia Di BiaseOlivia Di Biase
1
edited Feb 13 at 22:46
MarianD
1,4911718
asked Feb 13 at 22:17
Olivia Di BiaseOlivia Di Biase
1
edited Feb 13 at 22:46
MarianD
1,4911718
edited Feb 13 at 22:46
MarianD
1,4911718
edited Feb 13 at 22:46
MarianD
1,4911718
1,4911718
asked Feb 13 at 22:17
Olivia Di BiaseOlivia Di Biase
1
asked Feb 13 at 22:17
Olivia Di BiaseOlivia Di Biase
1
asked Feb 13 at 22:17
Olivia Di BiaseOlivia Di Biase
1
1
1
how is the chart created? What is the data source?
– teylyn
Feb 13 at 22:46
I don't think you can get Y value based on X, it's math. You should provide the data source and based on the data source to calculate the result.
– Lee
Feb 14 at 1:17
When you have a lot of points and short segments between them, like this, each line segment can be approximated with a straight line and be reasonably close. That's what Jon Peltier's answer does. If you want more accuracy, you could take a uniform section of the data containing no inflection points (maybe x+3 thru x=9), and fit a 2nd order polynomial trendline. That is likely to be a very good fit for that section. Use the resulting formula to estimate the Y value more accurately.
– fixer1234
Feb 14 at 7:12
add a comment |
1
how is the chart created? What is the data source?
– teylyn
Feb 13 at 22:46
I don't think you can get Y value based on X, it's math. You should provide the data source and based on the data source to calculate the result.
– Lee
Feb 14 at 1:17
When you have a lot of points and short segments between them, like this, each line segment can be approximated with a straight line and be reasonably close. That's what Jon Peltier's answer does. If you want more accuracy, you could take a uniform section of the data containing no inflection points (maybe x+3 thru x=9), and fit a 2nd order polynomial trendline. That is likely to be a very good fit for that section. Use the resulting formula to estimate the Y value more accurately.
– fixer1234
Feb 14 at 7:12
1
1
how is the chart created? What is the data source?
– teylyn
Feb 13 at 22:46
how is the chart created? What is the data source?
– teylyn
Feb 13 at 22:46
I don't think you can get Y value based on X, it's math. You should provide the data source and based on the data source to calculate the result.
– Lee
Feb 14 at 1:17
I don't think you can get Y value based on X, it's math. You should provide the data source and based on the data source to calculate the result.
– Lee
Feb 14 at 1:17
When you have a lot of points and short segments between them, like this, each line segment can be approximated with a straight line and be reasonably close. That's what Jon Peltier's answer does. If you want more accuracy, you could take a uniform section of the data containing no inflection points (maybe x+3 thru x=9), and fit a 2nd order polynomial trendline. That is likely to be a very good fit for that section. Use the resulting formula to estimate the Y value more accurately.
– fixer1234
Feb 14 at 7:12
When you have a lot of points and short segments between them, like this, each line segment can be approximated with a straight line and be reasonably close. That's what Jon Peltier's answer does. If you want more accuracy, you could take a uniform section of the data containing no inflection points (maybe x+3 thru x=9), and fit a 2nd order polynomial trendline. That is likely to be a very good fit for that section. Use the resulting formula to estimate the Y value more accurately.
– fixer1234
Feb 14 at 7:12
add a comment |
1 Answer
1
active
oldest
votes
You could do a linear interpolation, which I describe in a tutorial called Excel Interpolation Formulas on my blog. I've constructed your case below, with data I've manually digitized (i.e., "eyeballed"). This won't follow the nice curves along the XY plot, but it might still suit your purposes.
The X and Y data are in columns A and B. The X we want to find Y for is in the light gold shaded cell D18, and the resulting Y value is in E18. First we need to find which X value our desired X value falls on or after, so in cell E20 there is a simple MATCH formula to find this:
=MATCH(D18,A2:A27,1)
D18 is the value we're looking up, A2:A27 is the range we're looking for it, and 1 means this range is in ascending order and we want the largest value less than or equal to our sought value in D18.
My blog post cited above has the explanation for how the interpolation between point 6 and point 7 works here. The formula that does this in is cell E18:
=INDEX(B2:B27,E20)+(D18-INDEX(A2:A27,E20))*(INDEX(B2:B27,E20+1)-INDEX(B2:B27,E20))/(INDEX(A2:A27,E20+1)-INDEX(A2:A27,E20))
or wrapped to avoid horizontal scrolling:
=INDEX(B2:B27,E20)
+(D18-INDEX(A2:A27,E20))*(INDEX(B2:B27,E20+1)-INDEX(B2:B27,E20))
/(INDEX(A2:A27,E20+1)-INDEX(A2:A27,E20))
This X-Y pair is plotted as the orange cross hair against the blue dots and lines of the original data.
answered Feb 14 at 4:15
Jon PeltierJon Peltier
2,9221420
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1405471%2ffinding-a-point-in-an-excel-line-graph%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You could do a linear interpolation, which I describe in a tutorial called Excel Interpolation Formulas on my blog. I've constructed your case below, with data I've manually digitized (i.e., "eyeballed"). This won't follow the nice curves along the XY plot, but it might still suit your purposes.
The X and Y data are in columns A and B. The X we want to find Y for is in the light gold shaded cell D18, and the resulting Y value is in E18. First we need to find which X value our desired X value falls on or after, so in cell E20 there is a simple MATCH formula to find this:
=MATCH(D18,A2:A27,1)
D18 is the value we're looking up, A2:A27 is the range we're looking for it, and 1 means this range is in ascending order and we want the largest value less than or equal to our sought value in D18.
My blog post cited above has the explanation for how the interpolation between point 6 and point 7 works here. The formula that does this in is cell E18:
=INDEX(B2:B27,E20)+(D18-INDEX(A2:A27,E20))*(INDEX(B2:B27,E20+1)-INDEX(B2:B27,E20))/(INDEX(A2:A27,E20+1)-INDEX(A2:A27,E20))
or wrapped to avoid horizontal scrolling:
=INDEX(B2:B27,E20)
+(D18-INDEX(A2:A27,E20))*(INDEX(B2:B27,E20+1)-INDEX(B2:B27,E20))
/(INDEX(A2:A27,E20+1)-INDEX(A2:A27,E20))
This X-Y pair is plotted as the orange cross hair against the blue dots and lines of the original data.
answered Feb 14 at 4:15
Jon PeltierJon Peltier
2,9221420
add a comment |
You could do a linear interpolation, which I describe in a tutorial called Excel Interpolation Formulas on my blog. I've constructed your case below, with data I've manually digitized (i.e., "eyeballed"). This won't follow the nice curves along the XY plot, but it might still suit your purposes.
The X and Y data are in columns A and B. The X we want to find Y for is in the light gold shaded cell D18, and the resulting Y value is in E18. First we need to find which X value our desired X value falls on or after, so in cell E20 there is a simple MATCH formula to find this:
=MATCH(D18,A2:A27,1)
D18 is the value we're looking up, A2:A27 is the range we're looking for it, and 1 means this range is in ascending order and we want the largest value less than or equal to our sought value in D18.
My blog post cited above has the explanation for how the interpolation between point 6 and point 7 works here. The formula that does this in is cell E18:
=INDEX(B2:B27,E20)+(D18-INDEX(A2:A27,E20))*(INDEX(B2:B27,E20+1)-INDEX(B2:B27,E20))/(INDEX(A2:A27,E20+1)-INDEX(A2:A27,E20))
or wrapped to avoid horizontal scrolling:
=INDEX(B2:B27,E20)
+(D18-INDEX(A2:A27,E20))*(INDEX(B2:B27,E20+1)-INDEX(B2:B27,E20))
/(INDEX(A2:A27,E20+1)-INDEX(A2:A27,E20))
This X-Y pair is plotted as the orange cross hair against the blue dots and lines of the original data.
answered Feb 14 at 4:15
Jon PeltierJon Peltier
2,9221420
add a comment |
You could do a linear interpolation, which I describe in a tutorial called Excel Interpolation Formulas on my blog. I've constructed your case below, with data I've manually digitized (i.e., "eyeballed"). This won't follow the nice curves along the XY plot, but it might still suit your purposes.
The X and Y data are in columns A and B. The X we want to find Y for is in the light gold shaded cell D18, and the resulting Y value is in E18. First we need to find which X value our desired X value falls on or after, so in cell E20 there is a simple MATCH formula to find this:
=MATCH(D18,A2:A27,1)
D18 is the value we're looking up, A2:A27 is the range we're looking for it, and 1 means this range is in ascending order and we want the largest value less than or equal to our sought value in D18.
My blog post cited above has the explanation for how the interpolation between point 6 and point 7 works here. The formula that does this in is cell E18:
=INDEX(B2:B27,E20)+(D18-INDEX(A2:A27,E20))*(INDEX(B2:B27,E20+1)-INDEX(B2:B27,E20))/(INDEX(A2:A27,E20+1)-INDEX(A2:A27,E20))
or wrapped to avoid horizontal scrolling:
=INDEX(B2:B27,E20)
+(D18-INDEX(A2:A27,E20))*(INDEX(B2:B27,E20+1)-INDEX(B2:B27,E20))
/(INDEX(A2:A27,E20+1)-INDEX(A2:A27,E20))
This X-Y pair is plotted as the orange cross hair against the blue dots and lines of the original data.
answered Feb 14 at 4:15
Jon PeltierJon Peltier
2,9221420
You could do a linear interpolation, which I describe in a tutorial called Excel Interpolation Formulas on my blog. I've constructed your case below, with data I've manually digitized (i.e., "eyeballed"). This won't follow the nice curves along the XY plot, but it might still suit your purposes.
The X and Y data are in columns A and B. The X we want to find Y for is in the light gold shaded cell D18, and the resulting Y value is in E18. First we need to find which X value our desired X value falls on or after, so in cell E20 there is a simple MATCH formula to find this:
=MATCH(D18,A2:A27,1)
D18 is the value we're looking up, A2:A27 is the range we're looking for it, and 1 means this range is in ascending order and we want the largest value less than or equal to our sought value in D18.
My blog post cited above has the explanation for how the interpolation between point 6 and point 7 works here. The formula that does this in is cell E18:
=INDEX(B2:B27,E20)+(D18-INDEX(A2:A27,E20))*(INDEX(B2:B27,E20+1)-INDEX(B2:B27,E20))/(INDEX(A2:A27,E20+1)-INDEX(A2:A27,E20))
or wrapped to avoid horizontal scrolling:
=INDEX(B2:B27,E20)
+(D18-INDEX(A2:A27,E20))*(INDEX(B2:B27,E20+1)-INDEX(B2:B27,E20))
/(INDEX(A2:A27,E20+1)-INDEX(A2:A27,E20))
This X-Y pair is plotted as the orange cross hair against the blue dots and lines of the original data.
answered Feb 14 at 4:15
Jon PeltierJon Peltier
2,9221420
answered Feb 14 at 4:15
Jon PeltierJon Peltier
2,9221420
answered Feb 14 at 4:15
Jon PeltierJon Peltier
2,9221420
answered Feb 14 at 4:15
Jon PeltierJon Peltier
2,9221420
2,9221420
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1405471%2ffinding-a-point-in-an-excel-line-graph%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
how is the chart created? What is the data source?
– teylyn
Feb 13 at 22:46
I don't think you can get Y value based on X, it's math. You should provide the data source and based on the data source to calculate the result.
– Lee
Feb 14 at 1:17
When you have a lot of points and short segments between them, like this, each line segment can be approximated with a straight line and be reasonably close. That's what Jon Peltier's answer does. If you want more accuracy, you could take a uniform section of the data containing no inflection points (maybe x+3 thru x=9), and fit a 2nd order polynomial trendline. That is likely to be a very good fit for that section. Use the resulting formula to estimate the Y value more accurately.
– fixer1234
Feb 14 at 7:12