Forum Discussion

Copper Contributor

Feb 19, 2018

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...

SA Spelling Test.xlsx13 KB

Formulas & Functions

Need Help

Detlef_Lewin
Feb 19, 2018
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 DRABBLE

Copper Contributor

Feb 19, 2018

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

5.9

Cheers

David

Detlef_Lewin

Silver Contributor

Feb 19, 2018

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

David DRABBLE
Copper Contributor
Feb 19, 2018
Thank you again Detlef

I really appreciate your support.

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)