General Betting

Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
13 May 16 13:16
Date Joined: 02 Jun 03
| Topic/replies: 4,266 | Blogger: Gin's blog
Is there any way to format a number that has 3 decimal places to show the last digit in either a different size or colour?

e.g. if the value is 1.617 the “7” would be either a different size or colour.

I have looked at lots of stuff online regarding the custom format but it doesn’t appear to allow for this.
Pause Switch to Standard View Excel formatting question
Show More
Report Ghetto Joe May 13, 2016 7:55 PM BST
You could do it but reckon you'd need to use some VBA to do your formatting, you can display text in multiple colours but not formulas or numbers as far as I know.

Guess a lot depends what you're trying to do on the best way to acheive it, you could display the third decimal place in a different cell and format that or maybe look at conditional formatting if there are certain numbers you want to stand out.
Report longbridge May 14, 2016 5:30 PM BST
Not trivially.  What are you trying to achieve?  It's a bit simpler if you're just using this for display purposes rather than using it in later calculations (you can always have another cell, hidden if necessary, holding the raw number for computation purposes).
Report Gin May 18, 2016 12:20 PM BST
Thanks for the replies.

To be honest, I only want to achieve the above for cosmetic reasons. I have a row of numbers that I only really need to two decimal places but it is handy (but not essential) for me to know the third place. I am used to reading the numbers formatted as two places and find it distracting when formatted to three so I thought there might be a simple solution. Obviously, there is not so I will leave it.
Report longbridge May 18, 2016 1:37 PM BST
Simple solution is to add a column to the right of the one your numbers are in with the formula that it equals your column or whatever and then format one column to be 0.00 and one to be 0.000
Report Ghetto Joe May 18, 2016 7:04 PM BST
If it's just for display purposes why not just set a macro button to toggle between the column being formatted to 2 and 3 places.

Alternatively, it's easy enough to isolate the third decimal with a formua like

=MID(ROUND(A1,3),FIND(".",ROUND(A1,3))+3,1) (assuming your number is in A1)

You could easily set up a macro button to toggle between the column with the formulas   being displayed or not displayed.
Post Your Reply
<CTRL+Enter> to submit
Please login to post a reply.


Instance ID: 13539