INT Function Not Doing what I need

[Neil M]

On my spreadsheet I have the following formula:

=IF(G4>20.001,INT(G4/20),0)

The reason for the 20.001 is because I only need the result of G4/20 if the
number is over 20. It works for 20 (gives me 0) and works for 21 which gives
me 1.

However if I enter 40 it gives me 2 which is what I am telling it to do.

What I need is if it is <20 it should be zero. It should be 1 for 21 to 40
and 2 for 41 etc etc
(i.e. 61 = 3, 81 = 4, 101 = 5)

Simply put:
0-20 i need 0
21-40 i need 1
41 to 60 i need 2
61 to 80 i need 3

and so on.....

Am I using the right formula?

Thanks,
Neil

 

[Bernard Liengme]

This works better
=INT(G4/20)-(MOD(G4,20)=0)

The second term subtracts 1 when you have 20, 40, 60 .....
best wishes

 

[Fred Smith]

I think you want:

=int(g4/20)-1

Regards,
Fred.

 

[Neil M]

YEEEEE HAAAAAA!!!!!!!

THank YOU, Bernard!!

Most Appreciated!!!

Neil M