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" wrote:

On Fri, 27 Apr 2007, in microsoft.public.excel.charting,
Ira said:
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
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.