Custom Formatting of numbers - Excel 2003?

Question

I have formulas that compare this year's results to Budget and Prior Year.  There are cases where I get results such as 15300% or -8500%, because the numbers are so different.  I want to show anything that is over 1000% or less than -1000%, as M+ or M-.  I could do this with a long IF statement right in the cell, however this file goes out to other users and I would rather keep the formulas simple.  I have seen a way (and I've even done it, but can't find it now), where in Format-Cells, you can set up a custom format that says in effect: 

if it's >=10, show "M+"
if it's <=-10 show "M-"
otherwise show "0%" or whatever

Has anybody else seen this and can help with the exact syntax to use?

Thanks

voyager · Accepted Answer

Hi aladou, I think you can enter only 2 conditions in CUSTOM format box. Here is a workaround.

1. Two conditional formats for values over 1000% or less than -1000%

If A1>10%   ------Format the cell - Custom  "M +"
If A1<-10% ------Format the cell - Custom   ;"M -"

2. Right click on A1  - Format Cells - Number - Custom
0.00% ;[red](0.00%)

Hope this helps.

latricia · Answer

You don't need to install Office 2003. There's an option in Excel 2007 where you can choose the default saving format. In that section you can choose "Microsoft Office Excel 1997-2003" as the default xls format. The same thing works for MS Word.

garbo7441 · Answer

As one experienced for many years in budgeting, variations of 15300% and -8500%, year over year, would appear highly unlikely.

Are you sure that these variations are not truly 15.3% and -8.5% or 153% and -85%? (Anything over 100% is a  fairly high variation  unto itself.)

If so, just divide your cell value by 1000, or 100.

Trending News

Custom Formatting of numbers - Excel 2003?

3 Answers