How do I use a single cell value to chart a horizontal line in Exc

[Guest]

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.

 

[Del Cotter]

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.

 

[Del Cotter]

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?

 

[Jon Peltier]

http://peltiertech.com/Excel/Charts/AddLine.html

- Jon

 

[Guest]

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

 

[Del Cotter]

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.

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.

 

[Guest]

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

 

[Guest]

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

 

[Jon Peltier]

Oops!

- Jon

 

[Del Cotter]

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.