Solved Round and IF Function

July 8, 2014 at 17:46:27
Specs: Windows 7
I have a cell that is an IF function and it calculates if the value of another cell (A1) is in the thousands or in the millions. The result is a K or an M. I will refer to this cell as A2. The formula I used for this is: =IF(A1>999999,"M","K").
I now need help in entering a formula for another cell (A3). This cell needs to round up the value that is in A1. For example, if the value in A1 is 515,576, I need A3 to return as 516. If the value in A1 is 82,467, I need it to return as 82. But, if the value is in the millions, I need a value such as 1,263,958 to return as 1.264.
I found a formula from an old spreadsheet that will allow this to calculate, but it only allows for a return of the first three numbers if the value is in the hundred-thousands or if the value is in the millions. My issue is if the value is in the ten-thousands, then it will still pull the first three numbers, which obviously is not accurate. Also, my issue with this formula is that it does not round the number. The formula that I am trying to work from is: =IF(A2="K",LEFT(A1,3),(LEFT(A1,4)/1000).
Your help in figuring this out would be much appreciated as I am not an Excel genius (obviously) by any stretch of the imagination. Thank you! :)

See More: Round and IF Function

Report •

July 9, 2014 at 05:10:33
✔ Best Answer
How about this:


or to include your letters:


Be aware that the second formula will create a text string, not a number.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 11, 2014 at 07:51:32
These worked perfectly and exactly what I was looking for. The second one is awesome!! This way I can eliminate the column that identified the millions (M) and thousands (K).


Report •

Related Solutions

Ask Question