I have an excel file used to track specific personal information for a group
of people who work for me. I have a column for date of birth and in a
separate column, I would like to calculate a point value based on the
following:
DOB is younger than 05/01/1986 assign points value of 150
DOB is between 04/30/86 and 05/01/84 assign points value of 100
DOB is between 04/30/84 and 05/01/82 assign points value of 50
DOB is older than 04/30/82 assign points value of 0.

I found the IF formula, but I've only got it to work with one of the above,
not all of them. I've tried the OR formula too, but haven't been able to get
it to work correctly.
Any suggestions? I was hoping to get this into place and then be able to
change the dates in the formulas as time passes.
Thanks in advance for any assistance.

Hi Angie

I would put the three test dates in cells
A1 05/01/86
A2 05/01/84
A3 05/01/82

Then
=(DOB=A3)*50+(DOB=A2)*50+(DOB=A1)*50

--
Regards

Roger Govier


"Angie" wrote in message
...
I have an excel file used to track specific personal information for a
group
of people who work for me. I have a column for date of birth and in a
separate column, I would like to calculate a point value based on the
following:
DOB is younger than 05/01/1986 assign points value of 150
DOB is between 04/30/86 and 05/01/84 assign points value of 100
DOB is between 04/30/84 and 05/01/82 assign points value of 50
DOB is older than 04/30/82 assign points value of 0.

I found the IF formula, but I've only got it to work with one of the
above,
not all of them. I've tried the OR formula too, but haven't been able
to get
it to work correctly.
Any suggestions? I was hoping to get this into place and then be able
to
change the dates in the formulas as time passes.
Thanks in advance for any assistance.

=IF(A2=D$1,150,IF(A2=E$1,100,IF(A2=F$1,50,0)))
where your DoB is in A2
05/01/1986 is in D1
05/01/1984 is in E1
05/01/1982 is in F1
--
David Biddulph

"Angie" wrote in message
...
I have an excel file used to track specific personal information for a
group
of people who work for me. I have a column for date of birth and in a
separate column, I would like to calculate a point value based on the
following:
DOB is younger than 05/01/1986 assign points value of 150
DOB is between 04/30/86 and 05/01/84 assign points value of 100
DOB is between 04/30/84 and 05/01/82 assign points value of 50
DOB is older than 04/30/82 assign points value of 0.

I found the IF formula, but I've only got it to work with one of the
above,
not all of them. I've tried the OR formula too, but haven't been able to
get
it to work correctly.
Any suggestions? I was hoping to get this into place and then be able to
change the dates in the formulas as time passes.
Thanks in advance for any assistance.

Try something like this:

With
A1: (a DOB)

C1:C3 containing this list
5/1/1982
5/1/1984
5/1/1986

This formula assigns point values
B1: =SUM(COUNTIF($C$1:$C$3,"<="&A1))*50
OR
B1: =SUMPRODUCT(--(A1=$C$1:$C$3))*50

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Angie" wrote:

I have an excel file used to track specific personal information for a group
of people who work for me. I have a column for date of birth and in a
separate column, I would like to calculate a point value based on the
following:
DOB is younger than 05/01/1986 assign points value of 150
DOB is between 04/30/86 and 05/01/84 assign points value of 100
DOB is between 04/30/84 and 05/01/82 assign points value of 50
DOB is older than 04/30/82 assign points value of 0.

I found the IF formula, but I've only got it to work with one of the above,
not all of them. I've tried the OR formula too, but haven't been able to get
it to work correctly.
Any suggestions? I was hoping to get this into place and then be able to
change the dates in the formulas as time passes.
Thanks in advance for any assistance.