l
[C4J
;f(index(1,row())
>
index(coLO,2)
,"11,"***")
This
is
the formula that does
all
the work at producing the bars themselves.
It
must be
copied into every cell
in
the display area:
Echo,CeLL
c4,over
range
c4:n13
The formula itself needs some explanation.
It
uses the if( ) function
to
decide whether
to
display part of a
bar.
The if( ) function
takes
three arguments. The
first
is
an expression
which must give a numeric result
If
this result
is
non-zero the cell displays the second
argument, which may be
text
or numeric.
If,
however,
the result
IS
zero
the
third argument
is
displayed
In
the
cell.
Again this may be
text
or numeric.
In
each cell the formula compares the number
In
column one
of
that
row
(the value
labelling the vertical axis) with the number
In
row
two of that column (the value
to
be
displayed
in
the graph).
If
the
aXIs
label
IS
greater than the display
value,
the condition
is
true
(it
evaluates
to
1)
and nothing
is
displayed.
If
the axis label
is
less
than or equal
to
the display
value,
the condition
results
in
a value
of
zero,
and three asterisks are shown
In
the cell. The net result
is
that a bar
is
drawn
to
the correct height
in
each column.
Since a single formula
is
used
for
all
the cells
,n
the display, the cell reference can be
neither absolute nor
relative.
The reference
to
the display values must change
as
we
move from column
to
column
(i.e.
it
must be relative along a column) but must always
refer
to
row
two
as
we
move down, from
row
to
row
We
need a form
of
cell reference
which
IS
relative
With
respect
to
columns, but absolute with respect
to
rows.
Fortunately the
index()
function can be used
to
produce
thiS
effect
It
takes
two
parameters, a column number and then a
row
number, returning the contents of the
specified cell.
With
this
we
can construct any combination of absolute and relative
references. For example:
Function Column Ref.
Row Ref.
index(5,5)
absolute
absolute
index(col(
),5)
relative
absolute
Index(5,row(
»
absolute
relative
index(col( ),row(» relative relative
The function index(col( ),2) therefore returns the contents of the cell
in
row
two of the
current column, and index(1,row()) returns the contents of the cell
in
column one
(A)
of the current row
Try
putting different values
in
cells C2
to
N2 and see what effect they have
on
the display
ThiS
example enables
you
to
calculate the monthly payments due
on
a repayment
mortgage.
You
are asked
to
type
in
the amount of the loan, the interest
rate,
the length
of the loan
in
years and the month of the
first
payment The required repayments are
calculated and displayed, together with a complete repayment table for the whole period
of the loan. This table shows
you
the outstanding sum at the beginning of each month
until the loan
is
repaid.
Several of the calculations
in
the grid make use of values that are input by
use
of the
askn( ) function.
In
this
section
we
shall
produce the part
of
the grid that accepts your Input and calculates
the monthly repayments. When
you
have typed
in
the formulae and added a
few
figures
in
response
to
the askn( ) functions,
it
should look like Figure
5.7
[C1]
"MORTGAGE
REPAYMENT
CALCULATOR
[C2]
repH"=",
I en
(c1
))
[84]
"Loan
[C4]
askn
("Amount
of
loan")
The next three entries request the input
at
the interest
rate.
The original input
is
to
a
cell
(H4)
well
away
from the displayed portion of the grid
so
that
you
do
not normally
see it
You
type
in
a percentage
value,
e.g.
you
type
12
to
mean 12%.
The
value needed
by
the rest of the formulae
is
a fractional value
(e.g.
12% must be converted
to
0.12)
and this
is
calculated from the input value by the formula
in
cell
C5.
12/84
Examples
MORTGAGE
CALCULATOR
Mortgage
Repayment
Calculations
29
•
To Next Page
Loading...
Need help?
General
