INT Function Not Doing what I need
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 |
INT Function Not Doing what I need
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 |
INT Function Not Doing what I need
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 |
INT Function Not Doing what I need
YEEEEE HAAAAAA!!!!!!! THank YOU, Bernard!! Most Appreciated!!! Neil M |
All times are GMT +1. The time now is 09:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com