May 26 2022 04:18 AM
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?
Thanks,
Alfie
May 26 2022 04:40 AM
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.
May 26 2022 05:06 AM
May 26 2022 05:57 AM
@Alfieb1996 as @Sergei Baklan 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)/2
or
=IF( LEN(A1) > 4, A1/2000, A1/2)
May 26 2022 06:08 AM
Not 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.
May 26 2022 06:53 AM
May 26 2022 07:16 AM
May 26 2022 07:36 AM
May 26 2022 07:51 AM
May 26 2022 11:14 AM
May 26 2022 12:01 PM
@mtarler , nope, these are numbers formatted with thousand separator.
May 27 2022 01:24 AM
May 27 2022 04:18 AM