Forum Discussion
Nested Function IF, LEN, DIVIDE
Hi, I need to construct a formula that divides a number by 1000 if it is a certain length. I.e 5.139 needs to be divided by 1000 whereas 5.52 (in the same column) does not. Can anyone help? ...
That could be
=IF( LEN(A1) > 4, A1/1000, A1)however actual length is not exactly what you see in grid, it depends on formatting.
- That works perfectly, thank you. I should of stated. I need extra conditions. So I need to:
divide everything by 2, if it's length of 4 divide by 1000 (after it's been divided by 2) but if it's less than 4 then do not divide by 1000.
Thank you,
AlfieNot sure I understood what you'd like to achieve. Let assume you have number 1234.5. Length is 6. Divide on 1000, it'll be 1.2345. Length is exactly the same, 6, nothing changes.
- that bring up a great point. If you have a number like 333 after you divide by 2 it becomes 166.5 so it went from a length of 3 to a length or 4 numbers (but excel will actually see 5 characters) BUT if you have 1644 (starts with a length of 4) after you divide by 2 becomes 822 and now a length of 3. And when you are looking at decimal places excel will show 0.0xxx so should those 0s count? and then excel (default) will change to scientific method so it will be 1.23E-5 which will be another issue.
Alfieb1996 as SergeiBaklan mentioned using LEN on numbers is tricky and depends on formatting and such. But even simple example of 1234 vs 123.4 is 4 characters vs 5 characters and may throw off the formula. Maybe more info about exactly what and/or why you need to do that. But based on Sergei's answer here are updated options:
=IF( LEN(A1) > 4, A1/1000, A1)/2or
=IF( LEN(A1) > 4, A1/2000, A1/2)
