percentage expression ?

isildad

Active Member
Joined
Sep 22, 2004
Messages
325
I have a table with three columns.
Column one is my customer's name
Colum two is the yearly sales to this customer for 2003
Column three is the yearly sales to this customer for 2004

I use a form that adds / edits the records in the table.
I have added a text box to the form and called it +/- %
and gave it this expression =sum([2003]/[2004]) and that works but not the way I need it to.

What I need is (maybe) a different expression that would return the following results:

example 1 : 2003 = $100 ..... 2004 = $200 ..... +/-% = +100%
example 2 : 2003 = $100 ..... 2004 = $50 ....... +/-% = -50%

The + or - in the returned value in +/-% are desired but if this is a problem a red negative and black positive are OK too.
Can someone help on this problem ?

Would it be easier to use a query and have a "calculated" column in the query to do the math for the +/-% ?
If yes how do I do this ?
Thank you
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Change your formula

=(2004-2003)/2003

to get the percentage change against the prior year as a percent you must first get the difference between the years.
 

isildad

Active Member
Joined
Sep 22, 2004
Messages
325
Thank you Gibbs,

Took all this time to figure out you ment :
=([2004]-[2003])/([2003]).

:biggrin:

Any idea how to show a negative in red ? (i.e. -30%)
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
well, you got the idea....I should have been exact...my bad
 

isildad

Active Member
Joined
Sep 22, 2004
Messages
325

ADVERTISEMENT

Gibbs, ... just joking! I really thank you for your kind help. I'm new at Access .... knew that the formula was OK having tested it in Excel but had to use trial and error + Access Help to get it right.
Thanks again.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi
In answer to this question :
isildad said:
Any idea how to show a negative in red ? (i.e. -30%)
In the report design, select the box that contains the percentage formula, click on Format -> Conditional Formatting and you should be able to follow it from there.
HTH, Andrew. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,124
Messages
5,599,839
Members
414,342
Latest member
K Darrell Smith

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