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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
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%)
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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