 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / April 2007How do I use a single cell value to chart a horizontal line in Exc
Background: I am making a chart where monthly expenses are represented by vertical bars and the monthly budget is a constant, for the entire year, which is represented by a horizontal line. If a vertical bar for a particular month rises above the horizontal budget line then I have exceeded my budget for that month. What I can do: I can create this chart if I use a monthly budget row where each cell in the row has the same value. What I want to do: To keep my spreadsheet manageable, I want to have the monthly budget specified by a single cell, rather than an entire row, because it is a constant for the entire yerar. >I am making a chart where monthly expenses are represented by >vertical bars and the monthly budget is a constant, for the entire year, >which is represented by a horizontal line. >What I can do: I can create this chart if I use a monthly budget row where >each cell in the row has the same value. I don't see why this is such a problem; it looks like a very good solution to me. >What I want to do: To keep my spreadsheet manageable, I want to have the >monthly budget specified by a single cell, rather than an entire row, because >it is a constant for the entire year. If you feel you must, then you can create a named array formula whose size is the same as the number of columns in the expenses range, which is entirely filled with the value of the budget. Let us imagine that the expenses row is a range called EXPENSES and the budget value is held in a single cell called BUDGETVALUE. (you don't have to actually give these ranges names if you don't want to, it just makes it easier for me to describe the formula I'm about to recommend) Then create a named range called BUDGET, using the following formula in the "Refers to:" input box: =MMULT(BUDGETVALUE,EXPENSES/EXPENSES) (Explanation: EXPENSES/EXPENSES creates a row array the same size as EXPENSES, but filled with ones. Multiplying it by BUDGETVALUE makes the row array be filled with the budget value instead) Then create your series in the chart, as usual, but using the named range BUDGET instead of a cell range. I must repeat though, that the first idea is actually much *more* manageable, not less. But this solution gets you your single cell budget value with no wasted cells.  SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. On Fri, 27 Apr 2007, in microsoft.public.excel.charting, I said: >Then create a named range called BUDGET, using the following formula in >the "Refers to:" input box: > >=MMULT(BUDGETVALUE,EXPENSES/EXPENSES) Sorry, that formula falls over if the expenses row contains any zero values. This will work better: =MMULT(BUDGETVALUE,ODD(EXPENSES)/ODD(EXPENSES)) The ODD() rounding function will ensure the number will never be zero, no matter what. If anyone has a more elegant way of achieving the same result, can they post it? SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. Hi Del, I was able create the named range called BUDGET as you described. However I was unable to use BUDGET as a series value in the Source Data window of the Chart Wizard. -Ira > >I am making a chart where monthly expenses are represented by > >vertical bars and the monthly budget is a constant, for the entire year, [quoted text clipped - 33 lines] > first idea is actually much *more* manageable, not less. But this > solution gets you your single cell budget value with no wasted cells. >> create your series in the chart, as usual, but using the named >> range BUDGET instead of a cell range. I must repeat though, that the [quoted text clipped - 4 lines] >was unable to use BUDGET as a series value in the Source Data window of the >Chart Wizard. I think that, as with all uses of a named range in chart series, you need to explicitly refer to the sheet the data series is in. Because the chart isn't in any sheet, it can't just assume the name is in its own sheet, it has to be told. So if you've written the data in Sheet1, then you need to refer to the range as Sheet1!BUDGET instead of just BUDGET. See if that works for you. SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ > Background: I am making a chart where monthly expenses are represented by > vertical bars and the monthly budget is a constant, for the entire year, [quoted text clipped - 11 lines] > because > it is a constant for the entire yerar. Thanks Del, That pretty much did it. It turns out that the value that woked was ='FILENAME.xls'!BUDGET Now I have to get it so that the budget line will change when the BUDGET cell value changes. I think I have to read up on the Insert / Name functions. -Ira > http://peltiertech.com/Excel/Charts/AddLine.html > [quoted text clipped - 20 lines] > > because > > it is a constant for the entire yerar. Never mind what I said about the line not changing. I was changing the wrong cell. Your solution works beautifully. Thanks so much for the help. Ira > http://peltiertech.com/Excel/Charts/AddLine.html > [quoted text clipped - 20 lines] > > because > > it is a constant for the entire yerar. Oops! - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ > Never mind what I said about the line not changing. I was changing the > wrong [quoted text clipped - 30 lines] >> > because >> > it is a constant for the entire yerar. >Never mind what I said about the line not changing. I was changing the wrong >cell. Your solution works beautifully. Thanks so much for the help. You're welcome, and thanks for following up. SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.