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

Links? Where??
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),"")

Replies
7
Views
577
Replies
1
Views
69
Replies
8
Views
98
Replies
0
Views
42
Replies
1
Views
148

Threads
1,171,794
Messages
5,877,553
Members
433,265
Latest member
Umaratnam

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.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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