Quick excel help


#1

Hi!!

Doing a sheet…got real age and spelling age, is there a way I can calculate the difference.
Trick is I can’t just take one away as they are in 8.6 meaning eight years six months not 8.6 as a figure if that makes sense??


#2

not sure if this works but try it
=INT(A1)-INT(B1)+((A1-TRUNC(A1))-(B1-TRUNC(B1)))*12/10

where A1 and B1 are the numbers


#3

If the 8.6 represents 8 years and 6 months, then you need to convert the .6 to a decimal fraction.

=(A2-TRUNC(A2))*(12/10)+TRUNC(A2)

will convert it if the 8.6 is in A2.


#4

Convert it to months if the age is in a1 =(rounddown(a1,0)*12)+((a1-(rounddown(a1,0)))*10)

Do that to both numbers then subtract


#5

I have now learnt INT and TRUNC from this thread, puts my rounddown to shame


#6

Am I being dim - doesn’t the middle part need to be 10/12? This will convert 8.6 (8 years 6 months) to 8.5 which seems correct to me.


#7

12/10 > 1 so this results in 8.72

Here’s the example sheet I put together in that screenshot.

So the value in the last column (1.4) is 1 year, four months, which is the difference between the spelling age (7.2, 7 years and 2 months) and the reading age (8.6, 8 years and 6 months)


#8

You’ve proved that it works so that’s good enough for me :+1:


#9

This is way out of my comfort zone. Going to whack stuff in and see what it does!!


#10

That’s all Excel is so you’ll be fine


#11

Wait a sec, just had a thought.

How does it handle 10 or 11 months? If it’s 10 months, it’d be 8.10 which would be 8.1.


#12

Hmmmm, I am formatting as text to get the 8.10 etc in.

Have tried with your formulas so thanks everyone, but actually quicker to just do it in my head. This is entirely my fault not yours…


#13

The first thing you should do is shoot the person who formatted it that way in the first place.


#14

Or the person making me do this ridiculous form filling exercise.