Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How can I calculate percentage value +ve/-ve growth between 2 values when one or both are negative?

Posted on 2008-06-25
6
Medium Priority
?
1,892 Views
Last Modified: 2013-11-15
Hi,

I am trying to calculate the % increase / decrease between profit figures from previous to current year. Currently my formula is as follows:

If Sum ({@Current}) <> 0 and Sum ({@Previous}) <> 0 Then
((Sum ({@Current) - Sum ({@Previous}) / Sum ({@Previous})) * 100

It is fine if the current & previous years are both positive or if the previous year is positive and current is negative.

If however the previous year is negative then the values are correct but are incorrectly shown as +ve or -ve percentages (dependant upon current year figure)

I simply want to show a positive % if there has been an increase or a negative % if there has been a decrease, regardless of whether one or both figures are negative. So an example

Previous Year = -200
Current Year = 0

Increase = 100%

Does anyone know how to do this in Crystal?

Thanks in advance!!
0
Comment
Question by:deloptis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 2

Assisted Solution

by:mrider01
mrider01 earned 75 total points
ID: 21864736
It looks like you might have an error with your order of operations.
You need to group together your (current - previous), so your end equation should be:
[ (current - previous) / previous ] * 100
whereas it looks like you currently have
(current - previous / previous) * 100.

Maybe this is just a mistake when you brought your code over here.

But also think about how you'd measure growth of a negative number.  If you start off with -10 and end up with -5, did the value actually shrink by 50% even though it got bigger?  Mathematically, yes.  This is beacue your base value is negative.  A lot of times math looks wrong and doesn't make any sense.  But when it comes time to apply it, and use it, these tricky situations make sense.
0
 
LVL 27

Accepted Solution

by:
aburr earned 150 total points
ID: 21866095
I think that mrider01 has overlooked one pair of your () so your equation is as he says it should be. You can get out of your difficulty if you use the absolute value of your denominator.

I am not familiar with crystal so I do not know what the Sum function is but I think my solution will solve your problem.
I think that you do not meed to test that current is not zero

There is another difficulty when "previous" is very small. It is doubtful that under that condition the percentage means much. If previously you just broke even and you made a small change in your letterhead and the profit increased by one pound, your printer would want a big bonus because he had increased your profit by 1324%.
0
 
LVL 2

Expert Comment

by:mrider01
ID: 21866877
aburr, go ahead and count the number of right ")" and left "(" parentheses.  On the second line of code there are five left parentheses "(", and there are only four right parentheses ")".   The end parenthesis after {@previous gets is the end to the set that begins right at the begining of the {@previous.
I'll label each one for you.  o=open, c=close.
([1o]([2o]Sum([3o]{@Current)[3c]-Sum([4o]{@Previous})[4c]/Sum([5o]{@Previous})[5c])[2c]*100


0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:deloptis
ID: 21867155
Hi - I stripped down the formula to make it easier to read - but must have backfired on me. Actual formula as below:

If Sum ({@Current }, {orders_by_week.weekno}) <> 0 and Sum ({@Previous}, {orders_by_week.weekno}) <> 0 Then
((Sum ({@Current}, {orders_by_week.weekno}) - Sum ({@Previous}, {orders_by_week.weekno})) / Sum ({@Previous}, {orders_by_week.weekno})) * 100
0
 
LVL 27

Expert Comment

by:aburr
ID: 21868963
"aburr, go ahead and count the number of right ")" and left "(" parentheses.  On the second line of code there are five left parentheses "(", and there are only four right parentheses ")"
-
you are right.
Nevertheless I believe my solution to the problem stands.
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 150 total points
ID: 21896092
If you just want to make sure that the % is positive if the total goes up and negative if it goes down, try something like:

If Sum ({@Previous}, {orders_by_week.weekno}) <> 0 Then
  If Sum ({@Current }, {orders_by_week.weekno}) >=
   Sum ({@Previous}, {orders_by_week.weekno}) Then
//  Force the result to be positive
    Abs ((Sum ({@Current}, {orders_by_week.weekno}) -
     Sum ({@Previous}, {orders_by_week.weekno})) /
     Sum ({@Previous}, {orders_by_week.weekno}) * 100)
  Else
//  Force the result to be negative
    0 - Abs ((Sum ({@Current}, {orders_by_week.weekno}) -
     Sum ({@Previous}, {orders_by_week.weekno})) /
     Sum ({@Previous}, {orders_by_week.weekno}) * 100)


 Note that, as aburr mentioned, you shouldn't check for current <> 0.  If you do, your example where previous is -200 and current is 0 will produce 0, instead of 100.

 James
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We are taking giant steps in technological advances in the field of wireless telephony. At just 10 years since the advent of smartphones, it is crucial to examine the benefits and disadvantages that have been report to us.
Lithium-ion batteries area cornerstone of today's portable electronic devices, and even though they are relied upon heavily, their chemistry and origin are not of common knowledge. This article is about a device on which every smartphone, laptop, an…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question