On Fri, 13 Jun 2008 16:37:13 -0500 Don Daugherty <[EMAIL PROTECTED]> wrote:
> Jerry Feldman wrote:
> > On one sheet I have a monthly column of numbers where only the end of
> > the year is populated (for 50 years), as an example
> > A B
> > 7/31/2007 0
> > 8/31/2007 0
> > 9/30/2007 0
> > 10/31/2007 0
> > 11/30/2007 0
> > 12/31/2007 123.456
> > .
> > .
> > .
> > What I would like to do is to create another column that has 1 year for
> > each row:
> > X Y
> > 12/31/2007 123.456 (=A6)
> > 12/31/2008 456.678 (=A(6+12))
> >
> > I'm not sure how to set up a formula is column Y that will reference
> > the end of year numbers.
> >
>
> My preference would be to simply use, assuming you start columns X and Y
> in row 2, for example, Y2=vlookup(X2;$A$1:$B$999;2)
>
> However,to more closely match what you have suggested you want to do,
> you could use three columns, X, Y, and Z. Col X would contain the
> December dates, as you have illustrated, Cell Z2 and Y2 would contain
> the following entries: Z2 would read ="B"&TEXT(12*ROW(X2)-18;"###") and
> Y2 would read =Indirect(Z2). Actually, you could eliminate column Z and
> have Y2 read =Indirect("B"&TEXT(12*ROW(X2)-18;"###"))
Thanks much.
Unfortunately, those columns are not adjacent so vlookup is not
possible, but I certainly could easily copy the 2 columns to another
area for vlookup. The real underlying issue is that I had generated a
CSV from my code containing the raw numbers. Both my generated numbers
and the original spreadsheet numbers compare down to the 6th digit, but
diverge beyond that. One of the rules is that my figures must agree to
the maximum precision. Fortunately I found the difference.
The formula in cell B6 is =(V6)*y6*AA7.
aa7 is actually a discount rate.
In any case, I love the answers I get from this list, and I've gained
a tremendous amount of knowledge on how to use spreadsheets from both
the answers to my questions as well as others, and as I might have
mentioned, I got my first spreadsheet program (visicalc) directly from
Dan Bricklin back in 1979, but I never really had a need to do much
with it.
--
--
Jerry Feldman <[EMAIL PROTECTED]>
Boston Linux and Unix
PGP key id: 537C5846
PGP Key fingerprint: 3D1B 8377 A3C0 A5F2 ECBB CA3B 4607 4319 537C 5846
signature.asc
Description: PGP signature
