On my spreadsheet I have the following formula:

=IF(G420.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

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

The second term subtracts 1 when you have 20, 40, 60 .....
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Neil M" wrote in message
...
On my spreadsheet I have the following formula:

=IF(G420.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

I think you want:

=int(g4/20)-1

Regards,
Fred.

"Neil M" wrote in message
...
On my spreadsheet I have the following formula:

=IF(G420.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

YEEEEE HAAAAAA!!!!!!!

THank YOU, Bernard!!

Most Appreciated!!!

Neil M