Here is what I want to do; please tell me how to do it (or if it’s even possible).
Say I want to make an Excel spreadsheet, and then a graph, showing weight loss over a period of time.
So, my x axis is time (one increment = one week) and my y axis is weight (one increment = one pound).
Now suppose I have a goal that I want to attain: one pound per week for twenty weeks.
Entering my weight into Excel every week and converting that into a line chart is easy enough.
But, I’d like my target weight loss slope to be on there as well, so I can tell at a glance how my actual weight loss compares to my targeted weight loss.
I know I could manually enter each target data point. But the project I am really working on is not that straightforward.
So what I really need to know is this: Once I create my graph, can I somehow enter a straight line that goes from the upper right corner to the lower left corner of the graph? (actually, in this case it would have to go from the lower left to the upper right; the weight loss thing is just an illustration).
I am hoping I am missing a real simple answer to this dilemma.
I think the easiest way to do this is just to have another column of numbers with your desired weight for each week pre-entered into it. (or C1= “starting weight”, C2= C1-1, C3=C2-1, etc.) You could just as easily do it as day number (then C1= “starting weight”, C2= C1-1/7, C3=C2-1/7, etc.)
Then select the three columns A = time/week number, B = current weight, C = This week/day’s/ target weight and choose INSERT> CHART > LINE . Often it will pick up on the fact that A should be x-axis, but you can double click on the Chart legend to change the x-axis and y-axis references.
If you work out your theoretical slope and put it in say E1. Say your X values are in the A column (starting at A10) and your y values are next to them in B column couldn’t you just put this into C10 and past in to all the other rows:
$E$1*(A10-$A$10) + $B$10
Give you a line starting at your initial Y value following your ideal slope ending at the point Cn where B would have been if it had followed your slope exactly.
I appreciate the help, but this won’t work for what I want to do, because I don’t know the exact numbers to input. I really just want a straight, corner-to-corner line.
I’ll try to explain further, and more specifically:
The x axis will have 30 increments, numbered 1 through 30.
The y axis will have 11 increments, numbered 2 through 12.
Does this make sense as to why I can’t easily just input the numbers and end up with a straight line?
mmm
My first choice is what K364 answered, my second is: you can create a line with just 2 points. This is slightly easier if you have to change the scale. So somewhere on your spreadsheet, mark out 4 cells and input your points. On the chart, right click, edit data, add a new data series and choose your points. If you hide the markers nobody will know you only had 2 points.
Or a slightly more complicated way is to just specify the points, without drawing a line. Then add a trend line. The point of doing this is you can change the length of the trend line at whim, without moving your original points.
To try simplifying the matter a bit more, let’s say your goal is to lose 5 pounds a week, and you are charting weight in weekly time increments.
Down along Column A you list week 1, week 2, etc.
In column B you list actual recorded weights taken weekly.
In Column C are your target weights. You list the starting weight in the first row (C1). C2=C1-5, C3=C2-5, etc. The advantage is that you just write the calculation once, and then copy it down the column of cells below. The references will automatically change, so the calculation will always result in a 5 pound reduction from the value above.
You can indeed do what k364 suggests and draw an arbitrary line over your chart, but it will have no relationship at all with your data. If you calculate a formula for weight change, it will be accurate and to scale with your actual recorded charted data.