SOLVED

How do I display millions and billions like this $15M or $10B and still be able to calculate it?

%3CLINGO-SUB%20id%3D%22lingo-sub-307106%22%20slang%3D%22en-US%22%3EHow%20do%20I%20display%20millions%20and%20billions%20like%20this%20%2415M%20or%20%2410B%20and%20still%20be%20able%20to%20calculate%20it%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307106%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20create%20a%20chart%20for%20a%20client%20that%20uses%20these%20formats%2C%20such%20as%20%240.2B%20or%20%2410M%20and%20since%20it's%20a%20bar%20chart%2C%20I%20need%20it%20to%20pick%20up%20the%20values.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-307106%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308804%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20display%20millions%20and%20billions%20like%20this%20%2415M%20or%20%2410B%20and%20still%20be%20able%20to%20calculate%20it%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308804%22%20slang%3D%22en-US%22%3E%3CP%3ENevermind%2C%20I%20figured%20it%20out.%26nbsp%3B%20Thanks%20again.%26nbsp%3B%20It%20was%20a%20big%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308794%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20display%20millions%20and%20billions%20like%20this%20%2415M%20or%20%2410B%20and%20still%20be%20able%20to%20calculate%20it%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308794%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20response%2C%20but%20I'm%20not%20sure%20how%20to%20use%20your%20custom%20cell%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307138%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20display%20millions%20and%20billions%20like%20this%20%2415M%20or%20%2410B%20and%20still%20be%20able%20to%20calculate%20it%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307138%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20could%20use%20a%20custom%20cell%20format%20for%20your%20source%20data%2C%20not%20sure%20exactly%20where%20you%20want%20to%20break%20from%20M%20to%20B%20or%20how%20much%20you%20want%20the%20displayed%20numbers%20rounded%20though.%20Ex%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5B%26gt%3B99999999%5D%23.%23%23%2C%2C%2C%22%20B%22%3B%5B%26gt%3B99999%5D%23.%23%23%2C%2C%22%20M%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I need to create a chart for a client that uses these formats, such as $0.2B or $10M and since it's a bar chart, I need it to pick up the values.

3 Replies
Highlighted
Best Response confirmed by SJD-Fairfax (New Contributor)
Solution

You could use a custom cell format for your source data, not sure exactly where you want to break from M to B or how much you want the displayed numbers rounded though. Ex:

 

[>99999999]#.##,,," B";[>99999]#.##,," M"

Highlighted

Thank you for your response, but I'm not sure how to use your custom cell formula.

 

 

Highlighted

Nevermind, I figured it out.  Thanks again.  It was a big help!