Avatar of Matt Pinkston
Matt Pinkston
 asked on

excel formula to millions

I have a very large XLS Workbook with a lot of sheets and values.   I have been asked to round all dollar value fields to Millions...

in the past In  used a format like this  #,##0.0,,;;

Only problem is I have some values that are negative and I am showing those in red with parens around them.

what would be the best solution to resolve all my needs
Microsoft Excel

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Saurabh Singh Teotia

You can use conditional formatting to do this...

Like enclosed is the workbook for your reference where in column-f i applied this formatting..

What i did i selected my cells i said if value is greater then zero then do the conditional formatting i.e. change the format to 10 million otherwise leave it like that...

Saurabh..
Conditional-formating.xlsx
Matt Pinkston

ASKER
but I would like to go to millions on ALL values but in the case of negative values not loose the red paren feature

example

normal cell
$550,000,000.00 = $5.5M

($100,000,000.00) = ($100m)  in red of course
Matt Pinkston

ASKER
another issue I am having with #,##0.0,,;; is the values that were 0 show as blank
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Matt Pinkston

ASKER
and it converts negative numbers to blanks
Matt Pinkston

ASKER
I also found this format $#.##,," M";--

but for negative values I get blank
for 0 values O get $ M
Saurabh Singh Teotia

Right now i applied conditional formatting to only when the values are less then zero..If you want to apply it for..just apply a different formating when its smaller then zero that way you can retain the bracket and red color.

If you can post your workbook can help you in what you are looking for...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Danny Child

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Danny Child

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.