Can I make Excel add a line in a chart based on the value of a cell?

Question

Can I make Excel add a line in a chart based on the value of a cell?

Answer 1

Start at the same coordinate? Their values would have to be the same.

Answer 2

If you want to know how we can add the reference line in charts, check this link given below.........



http://www.exceltip.com/tips/how-to-add-a-reference-line-in-charts.html

Answer 3

Nanci,



I think that I understand what you want to do. Unfortunately, it is a little too complex to explain here. Basically, it requires dynamic range names in your chart. I thought this was interesting enough that I created a blog post to show how it can be done. Go to http://www.tvmcalcs.com/blog/excelblog.html and look at the top post.



I hope that helps,



Tim

Answer 4

Nanci;



Man, folks can make things complicated. Both of the answers that I have read below will work, but it's a bit more work than I would put into it.



Tim is right in one very important point. He references a specific number of cells in his blog. This is because you need to count the number of cells that have numbers in them. My workaround would be simply in cell a1 =count(d:d) where column d is the location of your weights. Then in b1 I would place =sum(d:d). Then in cell c1 you could put =b1/a1. This is the average weight.



Now the difficult part. What kind of chart are you using. Ideally these are in an XY chart. I love scatter charts. So versatile and easy to use. You will need two (2) reference cells with the same number and 1 cell with your average (or reveresed dependent on the axis that your average will reside). Place the numbers on the appropriate axis and bingo, dynamic chart.



Check out a sample I have on my groups page below called Gantt Chart.xls. The Today line does just that. A dynamic line that changes with the date.



P.S. Unlike some MS groups, Yahoo does not notify of replies to messages. If you want to contact the person individually you need to email them. Most folks place some kind of link on how to contact them in the event they want to be. I would not pester folks on an individual level, but there is no harm in shooting them an email with a link and a statement 'Hey, I need more help regarding an answer you gave me on Yahoo.'



God Bless



Frank Pytel



You can add a cell that calculates your average weight =average(sum(data)). Reference this cell in your chart, instead of inserting a value into the chart itself. This will dynamically update the chart whenever you add a new weight.



You could also run your data through a pivot table and use the chart wizard. One touch button on the toolbar. Great for quick charts. Only problem is that you must "Refresh" your pivot table in order to update the chart.



If you are using an XY scatter chart, it's a little more involved. Check out my document storage site for some dynamic examples of charts. Gantt.xls should help you to understand.



God Bless



Frank Pytel

Answer 5

Yes, you can add a line to automatically show the latest average:



1) add a column next to your daily weights, called "yearly average"

- the formula in every cell of this column is the same:

=AVERAGE(B$2:B$366)

where B2:B366 are the daily weights.



2) select the data with this yearly average data, and add it as a series in your chart.



Whenever you add a daily weight, the column with the average will automatically update, and so will the horizontal line in the chart.



I hope this helps -- add detail, or e-mail if you need more info.

.