On Wed, 23 Apr 2008 17:47:28 +1000 "Adrian Try" <[EMAIL PROTECTED]> wrote:
> Whoops . . . > > On Wed, Apr 23, 2008 at 5:35 PM, Adrian Try <[EMAIL PROTECTED]> wrote: > > > It should always be possible to work out a formula without using complex > > functions. The fun is, working out how! > > > > And getting it wrong can be fun too. Not. Sorry, I was interrupted towards > the end of figuring that formula out, and forgot something important. > > This one should work *all the time*: > > =(YEAR(B1)-YEAR(A1))-IF(MONTH(B1)=MONTH(A1);DAY(B1)<DAY(A1);MONTH(B1)<MONTH(A1)) =(YEAR(B1)-YEAR(A1))-IF(MONTH(B1)=MONTH(A1);DAY(B1)<DAY(A1);MONTH(B1)<MONTH(A1)) Actually, no, but close. Change A1 to $A$1. (And B1 to $B1). =(YEAR($B1)-YEAR($A$1))-IF(MONTH($B1)=MONTH($A$1);DAY($B1)<DAY($A$1);MONTH($B1)<MONTH($A$1)) But, If I put this in the context of the spreadsheet, it becomes a bit unwieldy. =IF(VLOOKUP(INT(YEARFRAC($A$1;$B1;1))-1;$mortcurves.G$3:H$137;2)<1;INT(YEARFRAC($A$1;$B1;1));"") =IF(VLOOKUP((YEAR($B1)-YEAR($A$1))-IF(MONTH($B1)=MONTH($A$1);DAY($B1)<DAY($A$1);MONTH($B1)<MONTH($A$1))-1;$mortcurves.G$3:H$137;2)<1;(YEAR($B1)-YEAR($A$1))-IF(MONTH($B1)=MONTH($A$1);DAY($B1)<DAY($A$1);MONTH($B1)<MONTH($A$1));"") But, if I convert this to a macro that can be used in both Excel and OO.o, it may be better than using =int(yearfrac()). -- -- 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
