# percentage comparisons

#### fenster

##### Board Regular
Hi folks,

Im working on a comparison on figures from 2004 compared to 2003 figures

example
2003 figure 15182.39 cell 2003!d7
2004 figure 20857.12 cell 2004!d7

difference 5674.73

and was wondering if any of u could help..

how can I show the 5674.73 as a percentage?
Can I make the percentage red if in minus? (if figure is in minus)
As I don’t have many figures for 2004 is there a way to get the formula to ignore if both figures are not greater than 0.00 or one of the cells is empty.

Thanks as always..

Davie.

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Sourdust

##### Well-known Member
An answer to the part about changing the minus figures to red could be in Format Cells/Custom, there should be an option for #,##0;[Red]-#,##0 or add the custom format #,##0%;[Red](#,##0%)

#### fenster

##### Board Regular
i also found isblank

=IF(ISBLANK('2004'!E9),"",'2004'!E9-'2003'!E9)

so its just the percentage bit im stuck at..

to show how many percent figures are up or down on previous year...

#### mark007

##### New Member
=IF(ISBLANK('2004'!E9),"",('2004'!E9-'2003'!E9)/'2003'!E9)

and format the column as %.

#### fenster

##### Board Regular

thats giving me a result ..but i dont know how to check it!

heres the sum:

35558.67(2004)
-
25743.51(2003)

=

9845.16 (difference)

the formula from mark007 shows 38.2%

so if the formula is doing what i want it to do then turnover on that set of figures is up 38% on previous year?

can someone confirm?

davie.

also isblank wont work with some cells because there is a '0' in some cells because of formula on the 2003 and 2004 sheets...

is there a =if is 0 ,"" , type function?

=AND(ISNUMBER('2004'!C11),'2004'!C11=0,'2004'!C11-'2003'!C11)

returns true , but it shows true in the cell 'TRUE' instead of just showing empty.

#### just_jon

##### Legend
See below --
Book1
ABCD
1200435558.6738.1%
2200325743.51
Sheet1

When wanting to compare 2 numbers, remember to divide by the number which you want to use as a baseline, that is -- the one which represents 100% of something. Once you get the division difference, subtract 1 [the 100% an identical value will generate] to yield the variance of the 2nd number in terms of a percentage of the 1st.

EDIT -- To check for a possible division by zero --
Book1
ABCD
1200435558.670.0%
22003
Sheet1

#### mark007

##### New Member

=IF(and('2004'!E9>0,'2003'!E9>0),('2004'!E9-'2003'!E9)/'2003'!E9,"")

#### fenster

##### Board Regular
thanks jon , dont think u could have made that more clearer..

do u have any suggestions on the last problem i have:

if cell 1 is>0 and cell 2 is >0 then subtract cell1 from cell 2 otherwise leave result cell empty..

btw the above is a new language and one day all vb will be written this way

#### fenster

##### Board Regular
hi mark ,

didnt see ur post ,

that works great ,

=IF(and('2004'!E9>0,'2003'!E9>0),('2004'!E9-'2003'!E9)/'2003'!E9,"")

if i use

=IF(and('2004'!E9>0,'2003'!E9>0),('2004'!E9-'2003'!E9)

then i get a false can i add something to the end of this formula to instead of false just show "" (empty)

#### mark007

##### New Member
Yes as per my example. Syntax for if statement is:

=IF(logical test, value if true, value if false)

If you omit the value if false argument it will just return false. To make it return something else - in your case a null string supply the last argument.

=IF(and('2004'!E9>0,'2003'!E9>0),('2004'!E9-'2003'!E9),"")

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,961
Messages
5,856,531
Members
431,819
Latest member
Tori Murphy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back