Calcuating % of increase - negative amts

I am trying to calculate the % of increase in revenue 
from one year to the next.

If Cell A2 (2003) is $100.00
If Cell A3 (2004)is $500.00
% of Increase in Cell A4 is 400% using the formula
=(A3-A2)/A2  

This works great but my problem is that in SOME cases, 
the previous year's revenue is in negative numbers.  In 
that case, it calculates the % of increase correctly but 
it makes it a negative % instead of a positive one.


If Cell A2 (2003) is ($100.00)
If Cell A3 (2004)is $500.00
% of Increase in Cell A4 is -600% using the formula
=(A3-A2)/A2  

This should be a positive 600%.  Is there a way I can get 
the %'s to be displayed correctly?

Thanks!

Mark

0
anonymous (74722)
6/15/2004 5:03:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
402 Views

Similar Articles

[PageSpeed] 17

Hi Mark
if your previous year is a negative value and you have now a positive
value you CAN'T calculate a percentage for this increase. Even an
increase from
$0.00
to
$100.00
in one year can't be calculated (infinite result)


--
Regards
Frank Kabel
Frankfurt, Germany


Mark wrote:
> I am trying to calculate the % of increase in revenue
> from one year to the next.
>
> If Cell A2 (2003) is $100.00
> If Cell A3 (2004)is $500.00
> % of Increase in Cell A4 is 400% using the formula
> =(A3-A2)/A2
>
> This works great but my problem is that in SOME cases,
> the previous year's revenue is in negative numbers.  In
> that case, it calculates the % of increase correctly but
> it makes it a negative % instead of a positive one.
>
>
> If Cell A2 (2003) is ($100.00)
> If Cell A3 (2004)is $500.00
> % of Increase in Cell A4 is -600% using the formula
> =(A3-A2)/A2
>
> This should be a positive 600%.  Is there a way I can get
> the %'s to be displayed correctly?
>
> Thanks!
>
> Mark

0
frank.kabel (11126)
6/15/2004 5:41:07 PM
Phantastic Frank, simply great !

Finest regards,

H.G. Lamy


"Frank Kabel" <frank.kabel@freenet.de> schrieb im Newsbeitrag
news:e7NpK$vUEHA.3512@TK2MSFTNGP12.phx.gbl...
> Hi Mark
> if your previous year is a negative value and you have now a positive
> value you CAN'T calculate a percentage for this increase. Even an
> increase from
> $0.00
> to
> $100.00
> in one year can't be calculated (infinite result)
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> Mark wrote:
> > I am trying to calculate the % of increase in revenue
> > from one year to the next.
> >
> > If Cell A2 (2003) is $100.00
> > If Cell A3 (2004)is $500.00
> > % of Increase in Cell A4 is 400% using the formula
> > =(A3-A2)/A2
> >
> > This works great but my problem is that in SOME cases,
> > the previous year's revenue is in negative numbers.  In
> > that case, it calculates the % of increase correctly but
> > it makes it a negative % instead of a positive one.
> >
> >
> > If Cell A2 (2003) is ($100.00)
> > If Cell A3 (2004)is $500.00
> > % of Increase in Cell A4 is -600% using the formula
> > =(A3-A2)/A2
> >
> > This should be a positive 600%.  Is there a way I can get
> > the %'s to be displayed correctly?
> >
> > Thanks!
> >
> > Mark
>


0
hglamy (20)
6/15/2004 6:37:51 PM
On Tue, 15 Jun 2004 10:03:01 -0700, "Mark"
<anonymous@discussions.microsoft.com> wrote:

>This works great but my problem is that in SOME cases, 
>the previous year's revenue is in negative numbers.  In 
>that case, it calculates the % of increase correctly but 
>it makes it a negative % instead of a positive one.
>
>
>If Cell A2 (2003) is ($100.00)
>If Cell A3 (2004)is $500.00
>% of Increase in Cell A4 is -600% using the formula
>=(A3-A2)/A2  
>
>This should be a positive 600%.  Is there a way I can get 
>the %'s to be displayed correctly?

Mark,

What you are trying to do is mathematically undefined and computationally
meaningless.

In stock analysis programs, the result returned is usually NA.


--ron
0
ronrosenfeld (3122)
6/15/2004 7:48:26 PM
Thanks for the feedback.  Of course, my next question 
would then be... why wouldn't Excel return the result as 
#N/A?


>-----Original Message-----
>On Tue, 15 Jun 2004 10:03:01 -0700, "Mark"
><anonymous@discussions.microsoft.com> wrote:
>
>>This works great but my problem is that in SOME cases, 
>>the previous year's revenue is in negative numbers.  In 
>>that case, it calculates the % of increase correctly 
but 
>>it makes it a negative % instead of a positive one.
>>
>>
>>If Cell A2 (2003) is ($100.00)
>>If Cell A3 (2004)is $500.00
>>% of Increase in Cell A4 is -600% using the formula
>>=(A3-A2)/A2  
>>
>>This should be a positive 600%.  Is there a way I can 
get 
>>the %'s to be displayed correctly?
>
>Mark,
>
>What you are trying to do is mathematically undefined 
and computationally
>meaningless.
>
>In stock analysis programs, the result returned is 
usually NA.
>
>
>--ron
>.
>
0
anonymous (74722)
6/15/2004 8:02:56 PM
On Tue, 15 Jun 2004 13:02:56 -0700, "Mark"
<anonymous@discussions.microsoft.com> wrote:

>Thanks for the feedback.  Of course, my next question 
>would then be... why wouldn't Excel return the result as 
>#N/A?
>
>

I think you'd have to ask the designers for a definitive answer.

My opinion is that although the concept of percentage increase from a negative
number is meaningless, Excel doesn't know what you are thinking when you plug
in the numbers for it to manipulate, so it can't tell you that, under the
circumstances from which you derived those numbers, the concept is not
meaningful.  Excel is only manipulating numbers.  GIGO.

However, if you are writing a stock analysis program, you know the origin of
your numbers, so it is easy to have your program come up with a NA (or NM or
NMR as some do).

--ron
0
ronrosenfeld (3122)
6/16/2004 1:16:29 AM
Reply:

Similar Artilces:

Why does priv1.edb increase in size when public folders are replicated?
We have an Exchange 2000 server on one Windows 2000 Server computer and Exchange 2003 on a different Windows 2003 server computer. We noticed that once we turned on replication for the public folders, the pub1.edb file increases in size, as does priv1.edb. There are no other mailboxes in priv1, so that doesn't account for the size increase. If we dismount priv1.edb, we can run eseutil /d and compact it to decrease the size. What you are seeing is normal behavior from my experience. When bringing up dedicated public folder servers, I've seen the mailbox store to grow to be quite...

Tax Estimator and AMT
Does the Tax Estimator make a calculation for AMT? ...

How do I increase a value if...
Hi, How can I set up a sheet when the value is greater than 1.5 the product will shown as 0.01 and every 1.5 it increases the 0.01. for example, if a1=1.5 then a1=0.01, if a1=3 then a1=0.02, if a1=4.5 then a1=0.03 and so on. Thx -- augustus ------------------------------------------------------------------------ augustus's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36352 View this thread: http://www.excelforum.com/showthread.php?threadid=561364 You could try a lookup. Create a table in C1 to D3 =VLOOKUP(A1,$C$1:$D$3,2) or a i statement =IF(A5=1.5,...

File Size Increases as Attachment
I am not sure if I am posting this in the correct group so I will post the same message in a Word group. I am using Office XP Pro (2002). I have a file that is 2,219kb. When I send it as an attachment it increases to 2 mb which makes it a large file to send. Why is this happening and is there a way to correct it? Thanks, "N Weir" <nweird@hotmail.com> wrote in message news:IbtRb.45914$Kg6.337846@news20.bellglobal.com... > I am not sure if I am posting this in the correct group so I will post the > same message in a Word group. I am using Office XP Pro (2002). I have a ...

How do I change the display of negative values to parentheses?
I have just started using Micrososft Office 2003 for Excel and noticed that the options for showing negative values is only a negative sign. I was wondering if there is a way to change this to parentheses as I was used to this idea in the older version. The comma (,) symbol should be on your Formatting Toolbar at the top of the screen. That will make -10 look like (10.00). If it isn't there, you can put it back by... 1) Select 'Tools' from the top menu 2) 'Customize...' 3) 'Commands' 4) In the 'Categories' box, select 'Format' 5) In the &#...

Cell Calcuations
I need help. I can't quite grasp writing formulas in Excel yet. I am trying to find the lowest dollar amount between cells A1,B1,C1 and then add that to cell D1 with the total in cell E1. Any info would be greatly appreciated!!!! Thanks, Bruce D. -- Bruce DiPaola =min(A1:C1)+D1 "Bruce D." <BruceD@discussions.microsoft.com> wrote in message news:8B0C0AEB-3FF4-4541-8836-B7F50124534C@microsoft.com... > I need help. I can't quite grasp writing formulas in Excel yet. I am trying > to find the lowest dollar amount between cells A1,B1,C1 and then add that to ...

How increase max # of rows/sheet in excel?
I have a huge report 250,000 rows generated for me at work (John Hancock). I use pivot tables to categorize the data. TAt this point I have to seperate the data into 5 worksheets, create 5 pivot tables, and then aggregate them. Is there a way to increase the maximum # of rows per sheet in excel above the 65,000+ default? For instance to 250,000+. That would save me a tremendous amount of time as I would only have to generate one pivot table. Hi no not possibe. 65536 is the maximum. Though with this much of data you sgould consider using a database (e.g. Access) and create pivot tabl...

Dynamics RMS 2.0 negative items
When I send items to the Stores from HQ the first day it shows the right quantity...buet after few days the quantity goes to negative....We check out Worksheets and we cant see any error. How we can track and solve this problem ? The Item Movement History report is a good tool for figureing this out. When you run the report for a single item model without the date filter, the sum of the items moved should equal the current stock status. "Prometeus68" wrote: > When I send items to the Stores from HQ the first day it shows the right > quantity...buet after few days the ...

How to increase the size of a Pivot Table Graph
I have created a very simple Pivot Table 3-D Pie Graph, but it shows on the screen in a size of approxiamtely 2 inches by 2 inches (at 100% window size). I click on the graph to make the four corner points highlight, but I can't drag them to increase the general size of my graph. Can pivot table graphs be increased in size, or are that fixed to one size? Many thanks, Hi did you use the icon on the pivot table toolbar to create the pivot graph? mine always come out at full window size ...also works if i choose this option after choose data / pivot table and pivot chart report and...

Customer.TotalSales & Negative Line Items
The Customer.TotalSales does not seem to reflect line items that hava a negative price (e.g. coupons). Does anyone have a work around for this? There's a 'Detailed Sales w/Return Values' report you can d/l from the CustomerSource reports library. Tom -- The worst words in business: "We''ve always done it that way" -- Stop Fishing for eMail. "Patrick" wrote: > The Customer.TotalSales does not seem to reflect line items that hava a > negative price (e.g. coupons). Does anyone have a work around for this? ...

Increase significant digits that can be stored in Enterprise
Enterprise reporting currently allows you to store only 4 significant digits, this should be increased to at least 8. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=9ddcaf...

Increase a user's mailbox send and receive attachment option
I have increase the user's mailbox to 20 MB (20000 KB) for SENT and RECEIVE of Attachments but it still does not work. Is there any other place I need to go except in the users ADUC Exchange General Option for attachments? Exchange 2003...thanks. Could it be the server you are sending the message to is rejecting it? "JV" wrote: > I have increase the user's mailbox to 20 MB (20000 KB) for SENT and RECEIVE > of Attachments but it still does not work. Is there any other place I need > to go except in the users ADUC Exchange General Option for attachments? ...

How do I increase the of significant digits given in the slope i.
Does anyone know how to increase the number of significant digits given for the slope of a trendline in the trendline equation in Excel? Double click the trendline equation; from the Number tab select a format that lets you specify the number of decimal places. Alternatively, use the LINEST function to get the value in the worksheet directly. [In versions prior to 2003, for certain data sets, the chart trendline gave better results than LINEST.] -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In arti...

Negative dates or times are displayed as ####
Negative dates or times are displayed as ####. The cell retains the negative value. How can I display negative time as "h:mm AM/PM" in red or with a negative sign? The cell should retain the negative value. You've got one reply to your other post in .programming. John Sheakley wrote: > > Negative dates or times are displayed as ####. The cell retains the > negative value. How can I display negative time as "h:mm AM/PM" in red or > with a negative sign? The cell should retain the negative value. -- Dave Peterson ec35720@msn.com ...

How do I show a negative control as a positive number
I have a calculated control on a report where the value is shown as -45. How can I format this number to show it as 45? Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via http://www.accessmonster.com "TonyWilliams via AccessMonster.com" <u56994@uwe> wrote in message news:a219779e31378@uwe... > I have a calculated control on a report where the value is shown as -45. How > can I format this number to show it as 45? > Thanks > Tony =Abs([FieldName]) where FieldNa...

"Share balance negative at some point"
I am trying to record a sale of some shares of a fund that I own. I have over 2900 shares of this fund and I am trying to record the sale of 68 shares. When I try to record it as a "sell" transaction I get an error message that says "The changes you are making will cause the share balance for 'Fundxxx" to become negative at some point. Please cancel or revise the transaction." The issue only affects this one fund and it will allow me to record the transaction as a "short sell". I do not understand why it will not let me record the transaction? Any ...

Newbie Question
Ok, I'm tearing my hair out. I've searched for about 6 hours (and I thought I was pretty good at searching/researching on the 'net before this) and apparently, I'm just blonde and that's that. It seems to me what I'm trying to do would be in a "Welcome to Excel 101" tutorial on the first page... argh! Anyway, I'm trying to get a final sum of two cells. Below, I'll try to give a step by step example of the possible scenarios I'm trying to resolve in one single cell, the sum of two other cells, which may contain double positives, double negatives...

Increasing mailbox limits
I am trying to increase the mailbox size of a user to 3000000kb but an error is returned "the value for issue warning at (kb) is not valid specify a value from 0 through 2097151" Does any one know how to resolve this? regards Stewart "Stewart Trussler" <StewartTrussler@discussions.microsoft.com> wrote: >I am trying to increase the mailbox size of a user to 3000000kb but an error >is returned "the value for issue warning at (kb) is not valid specify a value >from 0 through 2097151" > >Does any one know how to resolve this? Use ADSIEDIT...

How do I get Excel to display a negative value for time?
I am creating a set of worksheets analyzed with pivot tables and charts that track time delays in starting office procedures. Some of the procedures were begun before the scheduled time. How do I get Excel to show those as negatives or any other useful format? I am using Excell 2003. ...

negative [h]:mm:ss does not show on the x axis of a chart #2
If you have minus hh:mm:ss time on a chart or pivot chart the x axis does not display any values down it's axis. and when you hove over the bar, value is empty. In Excell 2003 it work fine in a normal chart, but not pivot chart. In Excel 2007 neither work. Also when you select the 1904 date system, the times fly through the roof. time is displayed as 1:00:00 on the worksheet, but within the pivottable / chart they are shown as 35089:00:00, formula in the cell = 3600 / 86400. fields / cells all are formatted as [h]:mm:ss ---------------- This post is a suggestion for Microsoft, and Micro...

How do I increase the text size of an Excel cell?
Hi guys, Situation: I had a memo size field in MS Access. When I copied and pasted it into an MS Excel cell the text gets truncated? I can't believe that Microsoft has had about 5-6 versions of Excel and hasn't fixed this simple problem. Mike Web Developer and Catholic Apologist http://www.faithfulchristians.net I think this is by design. Not everyone would want the whole column resized because of a single truncated cell. "Mike_Humphrey" <Mike_Humphrey@discussions.microsoft.com> wrote in message news:79662172-C13B-4708-AAF4-B02511AD79AA@microsoft.com... > H...

Custom Format- Negative Percentages
I'm looking for a custom format that will show percentages as (x%) instead of -x%. Thanks... You can use custom format and 0.00%;(0.00%) -- Regards, Peo Sjoblom "astrowaites" <astrowaites@discussions.microsoft.com> wrote in message news:ADCCF794-427D-46AB-AF93-661B9703E0FF@microsoft.com... > I'm looking for a custom format that will show percentages as (x%) instead of > -x%. Thanks... ...

BOM
Hi Folks I have defined a Labour Service Item in my Inventory Masterfile. As it is a service item it does not have a unit cost. I'm needing to use it as part of a BOM. When I do the assembly transaction I would like to cause a recovery to my Labour expense account by having access to the value for the labour item. I can see that for my conventional Sales Inventory items in the BOM the system will use the unit cost of my lot for those items, but my service item is a problem. If you cannot have a cost of that service item pull through onto the BOM, then what use is it to inc...

Excel formula to incrementally increase a value by 1 every 365 day
I need help with correct formula to take a starting date from one cell and place a formula in another cell that will increase the number by one for every 365 days. Employment date 5/30/2010 - 365 days past, new cell shows value "1" representing 1 year Another year passes and the new cell automatically updates to show the value "2" representing 2 years employment Another 365 days pass I need the value of the cell to automatically increase to "3" I tried the IF functions with TODAY() function but don't quite have it solved correctly. Tha...

Prevent negative numbers from changing to positive in calculation
On worksheet Page 1 (for example), I have referenced a group of numbers to calculate from worksheet Page 2. All the numbers in this reference group are negative numbers, but the calculation shows them as positive numbers. This is not the case for every calculation but occurs sporadically within this file. The formula of the negative numbers shows them with a '-' preceding the number. The formula of the calculation shows '=sum(cells referenced on Page 1). Why is this happening and how can it be fixed? HELP!! Thanks!! Maybe some of the entries are text, although they look ...