Nested Function IF, LEN, DIVIDE

[Alfieb1996]

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

[#2]

@Alfieb1996 

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.

[#3]

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,

Alfie

[#4]

@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)

[#5]

@Alfieb1996 

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.

[#6]

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.

[#7]

https://docs.google.com/spreadsheets/d/1iqFEbIesqlgXcswEr9Np962EOg-7S1cP/edit?usp=sharing&ouid=11195...

Hi both,

Thank you for your contributions. I want to basically divide certain data by 2. I've attached a sample from my workbook. So 'old' data needs to be divided by 2 in the 'new' column. You can see in A6 this is fine as it returns the same format (5.52 - 2.76) but in the others it still recognises 5.139 as 5,139 so gives an answer in the thousands (2569.5). Therefore, the values that exhibit thousands needs to be divided by 1000 to give the correct answer. I need all of this in the same column.

Many thanks!

Alfie

[#8]

@Alfieb1996 

It recognizes 5139 and formats it with thousands separator. There is no 5.139 entered.

[#9]

I see, some values in the worksheet are recognised differently. I guess I'll manually fix the inconsistencies. Many thanks for your time,

Alfie

[#10]

Is this a text file of some sort that is getting imported into excel? If that is the case you should fix how Excel recognizes those values on the import as opposed to trying to fix the problem after the fact.

[#11]

@mtarler , nope, these are numbers formatted with thousand separator.

[#12]

It's imported yes but as Sergei stated the numbers are just formatted inconveniently.

[#13]

My point is that if the data is imported and uses "." or "," one way but Excel is recognizing them the other, I'm pretty sure you can change that on some international number settings. @Sergei Baklan actually posts often about how to change those settings.

[#14]

I see :) That would make sense.