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! :)

How about this: =IF(A1<999999,ROUND(A1/1000,0),ROUND(A1/1000000,3))

or to include your letters:

=IF(A1<999999,ROUND(A1/1000,0)&"K",ROUND(A1/1000000,3)&"M")

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.

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). THANK YOU SO VERY MUCH! YOU ARE WONDERFUL!! :)

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History