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.

 

[Joe4]

I assume this is the part returning your value:
(N8-E7)*12
If you want just the integer part of it with no decimal, you can use the INT function, i.e.
INT((N8-E7)*12 )

 

[bbpvhc]

Yes, that is the return. I tried this and it doesn't calculate. It doesn't even act like a formula. It acts like a text cell and shows the formula.

=IF(E7<=60,INT((N8-E7)*12),"n/a")

 

[bbpvhc]

Yes, that is the return value. I tried the formula below, based on your suggestion, and it doesn't do anything. It doesn't even act like a formula. It acts like a text cell and just shows the formula.

=IF(E7<=60,INT((N8-E7)*12),"n/a")

 

[bbpvhc]

I also tried this and it still returns 28 months. I need it to return 27 months. It is rounding up and I need it to round down.

 

[bbpvhc]

Sorry...this is what I tried the 2nd time. I forgot to include it in the reply.

=IF(E7<=60,INT((N8-E7)*12))

 

[Joe4]

Yes, that is the return value. I tried the formula below, based on your suggestion, and it doesn't do anything. It doesn't even act like a formula. It acts like a text cell and just shows the formula.

=IF(E7<=60,INT((N8-E7)*12),"n/a")

That means that the cell is formatted as text.
Change the cell format from "Text" to "General", then re-enter the formula.

 

[Joe4]

You seem to be a bit all over the place here.

Please tell us exactly what is in each of these cells:
E7
N8

 

[bbpvhc]

As I stated in the original post, E7 is the person's age (For example, 57.7, which is 27.6 months from 60). N8 is 60. So, the formula is supposed to calculate the 27.6 months, but I need it to round down to be just 27 months. Again, with the INT function, it is rounding up to 28 months. I need it to round down.

 

[Scott Huish]

INT always rounds down and does in this case as well.

Book1
	E	F	G	H	I	J	K	L	M	N
7	57.7			27
8										60
Sheet1

Cell Formulas
Range	Formula
H7	H7	=IF(E7<=60,INT((N8-E7)*12),"n/a")