As the title of the topic says, I would like an excel formula to check if a number divided by 1000 is an integer?
So, if the result must be 1, 2, 3 ... , in this case the excel cell will display "R1" and if the result also contains decimals to return, suppose, the word "R2"?
How can I do that? A little help please.
If I understand correctly
Try =IF(MOD(A1/1000,1)=0,"R1","R2")
Try this
Formula:
Please Login or Register to view this content.
OR
Formula:Please Login or Register to view this content.
Last edited by saravnepali; 11-28-2019 at 04:10 AM.
If you think someone helped you, click on the "* Add Reputation" as a way to say thank you.
If your problem is solved, go to Thread Tools and select Mark This Thread Solved
Thanks for reply but does not work any formula.
Let's recap, if I have a number 1000 in cell A1, this number divided by 1000 results in "1", which means "R1". The same R1 must also be displayed if the box A1 = 2000 or 8000, since dividing those values by 1000, results in 2 and 8, which are integers.
The value "R2" should be displayed only if the result is in decimals, for example: 0.5; 1.5, 2.2, 8.5 ...
I hope that now I was more understandable!
Please post a sample sheet with expected results so we can stop guessing . Thanks
thats what the above formlas are meant to do and it works properly.Originally Posted by public
Not sure why its not working for you or you are after something else.
I have attached an excel file where in the excel boxes B1 ... B3 I have introduced the three formulas recommended in this topic. If you try to change the value in cell A1, you will see that in boxes B1 ... B3 nothing changes.
We assumed that the number would be on Range A1. But your sheet doesn't reflect that.
You have the A1 blank, why formula is NOT working, as it was taking A1 as zero.
See the attached where the number is in A1.
For example, cell A1 can take values from 0 to 16000.
I don't know why your formula does not work if cell A1 = 0, normally the result is 0 and in this case, so R1 should be displayed. But as you surely noticed, if A1 = 1500, R2 is not displayed in cells B1 and B2. Also, when A1 = 1000, cell B3 should display R1, what is not happening? !! So, all three formulas are not correct !!!
It seems that I have created a formula that meets my requirements -it may be useful to others!
=IF(OR(A1/1000=0;A1/1000=1;A1/1000=2;A1/1000=3;A1/1000=4;A1/1000=5;A1/1000=6;A1/1000=7;A1/1000=8;A1/1000=9;A1/1000=10;A1/1000=11;A1/1000=12;A1/1000=13;A1/1000=14;A1/1000=15;A1/1000=16);"R1";"R2")
You have done it the hard way.
Attached clearly shows that IF A1 = 0,1000,2000 OR 3000 etc it shows R1 & R2 for other values.
From WHAT I CAN SEE, ALL THREE FORMULAS WORK PERFECTLY FINE.
That's not a very nice formula, public!! Can you please re-state EXACTLY what you want?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
The problem was that he uses ";" rather than "," .
And no one noticed...
Hate this stuff, why not formalizing all the Excel Versions to ";" ?
John.
"I excel at jumping to conclusions"
Do you use ; in Brazil? I thought it was ONLY used in mainland Europe.
Yes, it's actually used in every country that uses "," as a decimal separator...
So basically every country except USA, CANADA, Switzerland, Liechtenstein, Mexico, Caribbean Islands, Australia, New Zealand, China, Japan, Malaysia, Singapore, Sri Lanka, The Philippines, UK and some small others in Africa
@public
As noted by jomaor1 in post 13, try using one of the formulae from posts 2 or 3 but change all the commas ',' to semi-colons ';' - they should then work for you.
@jomaor1
Well spotted.
Mainland Europe and ex-colonies almost all use ',' as the decimal separator. Almost all the British Commonwealth and ex-Empire - so including the USA - uses '.' as the decimal separator. That includes India, so it's a majority of the world population, though very possibly not a majority of the Excel-using population!
However, since using the semi-colon ';' as a separator in Excel formulae would not prevent still using the '.' as decimal point, like you I'm not sure why MS doesn't just standardise on using ';' in formulae.
I've run up against this problem often enough that I even added a note in my signature a couple of years ago!
Last edited by Aardigspook; 11-28-2019 at 02:18 PM.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Post #7:The reason is: "The value 3500 in attached workbook in post #7 is not in A1, but in A4. The formulas refer to A1"If you try to change the value in cell A1, you will see that in boxes B1 ... B3 nothing changes
Another formula:Put the value in A1Formula:Please Login or Register to view this content.. (Not in A4).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks