percentage comparisons

fenster

Board Regular
Joined
Nov 11, 2002
Messages
98
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Sourdust

Well-known Member
Joined
Sep 15, 2002
Messages
769
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
Joined
Nov 11, 2002
Messages
98
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
Joined
Feb 16, 2004
Messages
40
=IF(ISBLANK('2004'!E9),"",('2004'!E9-'2003'!E9)/'2003'!E9)

and format the column as %.

:)
 

fenster

Board Regular
Joined
Nov 11, 2002
Messages
98

ADVERTISEMENT

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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Feb 16, 2004
Messages
40

ADVERTISEMENT

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

:)
 

fenster

Board Regular
Joined
Nov 11, 2002
Messages
98
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 :biggrin:
 

fenster

Board Regular
Joined
Nov 11, 2002
Messages
98
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
Joined
Feb 16, 2004
Messages
40
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),"")

:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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

Which adblocker are you using?

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
Top