Forum Discussion
Solved
Calculate a Spelling Age
Hi,
I am trying to calculate a spelling age of students...
I need to input birth dates, date of test and a score (12 to 53)... and have a spelling age as my result.
The table would need to c...
David,
maybe something like this?
F5 =YEARFRAC(C5,D5) H5 =LOOKUP(G5,DATA!$A$4:$A$45,DATA!$B$4:$B$45) I5 =QUOTIENT(H5-F5,1) J5 =ROUND(MOD(H5-F5,1)*12,0)
David,
maybe something like this?
| F5 | =YEARFRAC(C5,D5) |
| H5 | =LOOKUP(G5,DATA!$A$4:$A$45,DATA!$B$4:$B$45) |
| I5 | =QUOTIENT(H5-F5,1) |
| J5 | =ROUND(MOD(H5-F5,1)*12,0) |
Thank you so much Detlef
You are very clever indeed.
One thing I noticed is that the values come out positive in I5/J5 when the year value is 0 but the month is actually negative. Can this be altered?
For example: if I make the score 12, the spelling age is 5.9 but the actual age is 6.4.
1/10/2010 2/03/2017 6.4 12 5.9 0 6 Cheers
David
Maybe another way would be better. Calculate only in months.
And if you need to show the result in years and months then use L5 and M5.
F5 =YEARFRAC(C5,D5)*12 H5 =LOOKUP(G5,DATA!$A$4:$A$45,DATA!$B$4:$B$45)*12 J5 =H5-F5 L5 =QUOTIENT(J5,12) M5 =J5-L5*12
Thank you again Detlef
I really appreciate your support.
