and if based on age assistance

[Aeternus]

Hi,
I have created a formula that calculates the age of a person based on the date entered:
=DATEDIF(B9,TODAY(),"Y")
=DATEDIF(B9,TODAY(),"YM")

and then i put the data into another cell:
=VALUE(AA8)&"."&(AA9)

so for example i have the age of 4.5.
Then I want to see if the age is between which age group so i have defined 2 age groups(min and max in different cells)

[TABLE="width: 500"]
<tbody>[TR]
[TD]0.3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]
So I use this formula:
=IF(AND(AB12>Z21,AB12<Y21),V20,IF(AND(AB12>Z22,AB12<Y22),V21,"ERROR"))

However it seems that it cannot calculate it properly, if I use 'OR' it works however I need it to match both min/max to hit the correct age group
I'm sure i'm missing something basic, I even tried this: '=AND(AB12<=Y22,AB12>=Z22)' however it returned 'FALSE' so I know it cannot read the cell that gives the age properly - could it be the '.' there or maybe the cell category?

Thanks in advance for your help

 

[shg]

You'd be better off using

=DOLLARFR(DATEDIF(B9, TODAY(), "m")/12, 12)

That would give a numeric result with ascending ages in lexical order (33.08, 33.09, 33.10, 33.11, 34.00, ...)

 

[Aeternus]

Thanks that worked great!
How can i add to the function to round up a month if number of days is 15 or above?
so if the age is 3 years 5 months and 15 days it will be 3.06?
Thanks!

 

[shg]

Add 15 to B9 in the formula.

 

[Aeternus]

Thanks I will try that now,
Also I was wondering in my last formula what is the easiest way to do the following:
I have a chart for each age, and in that chart i have an avg score based on a score of a test.
so for example for age 2 that scored 90 he will get avg score of 5.
I have around 8 age charts, I thought a lot of IF(AND) will do the trick but might be a bit messy.


Thanks

 

[shg]

Sorry, I don't undertsand that at all.