Rounding issue with IF statement

[bbpvhc]

I am trying to create a calc worksheet for a pension plan. I need to calculate how many months someone is from being 60-years old in order to find a reduction rate in in a table. When working properly, the formula will look at the person's age, calculate how many months they are from being 60. Another cell will do a vlookup on the number of months to find the reduction rate. The problem I am having is that the result of the formula is not a true month. For example, the result might be 27.4 months. I need the next cell to look up 27 months and ignore the decimal. I was trying to include ROUNDDOWN in the formula so that the result is always an exact month, but I can't get it to work. Where would I put the ROUNDDOWN at? Is there a better way to achieve this?

=IF(E7<=60,(N8-E7)*12,"n/a")
Note: N8 is 60 and E7 is their age in years with one decimal. For example, 57.7 is in E7, which would be 27 months before age of 60. It is coming back as 27.6, so the vlookup can't find 27.6 because it needs to find 27.

 

[bbpvhc]

It is not doing that for me

 

[bbpvhc]

It is not doing that for me. I can't even get that formula to work unless I delete the "n/a" portion. If I do delete the "n/a" portion, it returns as 28 still.

 

[Scott Huish]

My guess is that the value in E7 isn't exactly 57.7, just formatted that way.

So, try this:
=IF(E7<=60,INT((N8-TRUNC(E7,1))*12),"n/a")

 

[bbpvhc]

.

 

[bbpvhc]

You're probably right. That's doing the same thing though. It won't calculate anything.

 

[bbpvhc]

Here is the formula in E7: =((YEAR(B6)-YEAR(B7))*12+MONTH(B6)-MONTH(B7)+IF(DAY(B7)=1,0,-1))/12

I tried to get that to load as a value in a different cell to go from there, but even =TEXT(E7,0) is rounding up. UGH!

 

[Scott Huish]

Why did you use TEXT? If you want to see what the cell actually contains format the cell as General.
That cell actually, like I said before doesn't contain 57.7, the actual value is 57.6666666666667. This is why your result is coming out incorrectly.
So, try this:
Change the formula you gave to =ROUND(((YEAR(B6)-YEAR(B7))*12+MONTH(B6)-MONTH(B7)+IF(DAY(B7)=1,0,-1))/12,1)

Book1
	B	C	D	E	F	G
1						57.6666666666667
2						57.7
3
4
5
6	7/1/2015
7	11/1/1957
Sheet1

Cell Formulas
Range	Formula
G1	G1	=((YEAR(B6)-YEAR(B7))*12+MONTH(B6)-MONTH(B7)+IF(DAY(B7)=1,0,-1))/12
G2	G2	=ROUND(((YEAR(B6)-YEAR(B7))*12+MONTH(B6)-MONTH(B7)+IF(DAY(B7)=1,0,-1))/12,1)