software

I am trying to write a formula that is able to return the earliest possible
year of retirement based on years of service and age. The conditions are
summarized in the following table:

AgeYears of Service
20056036
200660,536,5
20076137
200861,537,5
20096238
201062,538,5
20116339
201263,539,5
20136440
201464,540

To retire in any given year, someone must simultaneously meet the two
conditions. An example, for someone to retire in 2009 they must be at least
62 years old and have 38 years of service.

Any ideas?

Thanks in advance!

=if(and(agegt;=62,YoSgt;=38),quot;Yesquot;,quot;Noquot;)

quot;Johnhaxquot; wrote:

gt; I am trying to write a formula that is able to return the earliest possible
gt; year of retirement based on years of service and age. The conditions are
gt; summarized in the following table:
gt;
gt; AgeYears of Service
gt; 20056036
gt; 200660,536,5
gt; 20076137
gt; 200861,537,5
gt; 20096238
gt; 201062,538,5
gt; 20116339
gt; 201263,539,5
gt; 20136440
gt; 201464,540
gt;
gt; To retire in any given year, someone must simultaneously meet the two
gt; conditions. An example, for someone to retire in 2009 they must be at least
gt; 62 years old and have 38 years of service.
gt;
gt; Any ideas?
gt;
gt; Thanks in advance!

Assuming we know the age(D2) and service(E2) of the candidate, then with the
following table in A2:C11, the we can find if the candidate is eligible for
retirement:
This assumes ages are in half-yearly increments and matches an entry in the
table

in F2:

=IF(E2gt;=VLOOKUP(D2,A2:C11,2),VLOOKUP(D2,A2:C11,3), quot;Too early!quot;)

If length of service is gt;= to service amount in table for this age, then
return Year, else quot;Too early!quot;

A B C
AgeServiceYear
60.0036.002005
60.5036.502006
61.0037.002007
61.5037.502008
62.0038.002009
62.5038.502010
63.0039.002011
63.5039.502012
64.0040.002013
64.5040.002014

HTH

quot;Johnhaxquot; wrote:

gt; I am trying to write a formula that is able to return the earliest possible
gt; year of retirement based on years of service and age. The conditions are
gt; summarized in the following table:
gt;
gt; AgeYears of Service
gt; 20056036
gt; 200660,536,5
gt; 20076137
gt; 200861,537,5
gt; 20096238
gt; 201062,538,5
gt; 20116339
gt; 201263,539,5
gt; 20136440
gt; 201464,540
gt;
gt; To retire in any given year, someone must simultaneously meet the two
gt; conditions. An example, for someone to retire in 2009 they must be at least
gt; 62 years old and have 38 years of service.
gt;
gt; Any ideas?
gt;
gt; Thanks in advance!

With your existing Table in say J7:L16 (excluding headers)
In Cell G4 - enter Year you wish to test for, say 2005
Birthdate in Cell C7 11/27/1944
EmployDate in Cell D7 6/6/1966
In cell E7 (with header - Do you Qualify?)enter:

=IF(AND(DATEDIF(C7,DATE($G$4,1,1),quot;yquot;)gt;=VLOOKUP($G $4,$J$7:$L$16,2,FALSE),DATEDIF(D7,DATE($G$4,1,1),quot; yquot;)gt;=VLOOKUP($G$4,$J$7:$L$16,3,FALSE)),quot;YESquot;,quot;N Oquot;)
watch for wrapping after pasting..
HTH,
Jim May
quot;Johnhaxquot; wrote:

gt; I am trying to write a formula that is able to return the earliest possible
gt; year of retirement based on years of service and age. The conditions are
gt; summarized in the following table:
gt;
gt; AgeYears of Service
gt; 20056036
gt; 200660,536,5
gt; 20076137
gt; 200861,537,5
gt; 20096238
gt; 201062,538,5
gt; 20116339
gt; 201263,539,5
gt; 20136440
gt; 201464,540
gt;
gt; To retire in any given year, someone must simultaneously meet the two
gt; conditions. An example, for someone to retire in 2009 they must be at least
gt; 62 years old and have 38 years of service.
gt;
gt; Any ideas?
gt;
gt; Thanks in advance!