#,##0, k or #,##0.0, k ? Why not both?

Conditional Custom Formatting in #Excel

Got the following question:

This gives me £28.8 K (if 28800 is input) and £28.0 K if 28000 is input. I was rather hoping that if there was no decimal after the thousand, ie £28 K, this would end up as £28 K, and not £28.0 K, but I guess that is not possible.

Here’s a screenshot illustrating the question:

Excel Tip - Excel Format with Conditions

Is it not possible????? Nothing is impossible, especially during Christmas.  🙂

Before we dive into the steps to achieve it, let’s see the custom formats for $28.0 k and $28 k first: 

$#,##0.0, k --> $28.0 k

$#,##0, k --> $28 k

See the tiny difference?  

Let’s put them into action.  You may download a to Sample File – Conditional Custom Format to follow along.

1. Apply custom format for $28.0 k

Select the range –> Ctrl+1 to open the Format Cells –> Number tab –> Custom –> Type $#,##0.0, k –> OK

Excel Tip - Excel Format with Conditions 1

Tip: The negative part in the custom format is optional here.  If omitted, Excel will assume the format for negative number be the same as that for positive number.  Want to know more about Custom Format?  Matthew Kuo of mbaexcel has a detailed post on it: http://www.mbaexcel.com/excel/how-to-use-excel-custom-number-formatting/

Now we should get the following:

Excel Tip - Excel Format with Conditions 2

However it has not taken care of the whole thousand numbers yet.

No worries.  Conditional Formatting to rescue.

2. Set the Condition Formatting

Select the range (A1:A6 in our example) –> Home Tab –> Conditional Formatting –> New Rules…

 

Excel Tip - Excel Format with Conditions 2Excel Tip - Excel Format with Conditions 3

The trick is to set the rule to identify if the number is in whole thousands.  To do that, we use the formula:

=mod(A1,1000)=0  
'this formula checks if the residue by dividing 1000 is equal to 0

Note: A1 is a relative reference.  Also ensure A1 is the active cell of the range selected.

Excel Tip - Excel Format with Conditions 4

When the condition is fulfilled, then apply the custom format for $28 k (no decimal).

Excel Tip - Excel Format with Conditions 5

3. Now we’ve got the results.

Excel Tip - Excel Format with Conditions

Isn’t it a Christmas miracle? 🙂

Advertisements
This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

6 Responses to #,##0, k or #,##0.0, k ? Why not both?

  1. xlor.la says:

    Thanks for this christmas present! greetings from Lima, Perú!

    Like

  2. Yea thanks for this christmas present!, cheers from Lima Perú!

    Like

  3. Anne Broxton says:

    Thank you so much for the Christmas miracle. This has worked exactly as I wanted. Wishing you and yours a very Happy Christmas and a peaceful New Year.

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s