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 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
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
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.
- 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.
- https://docs.google.com/spreadsheets/d/1iqFEbIesqlgXcswEr9Np962EOg-7S1cP/edit?usp=sharing&ouid=111952071670621667207&rtpof=true&sd=true
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
