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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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%)
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
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)
 
Upvote 0
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),"")

:)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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
Back
Top