Solved

Multiplication by negative value that should be positive

Posted on 2014-01-16
13
175 Views
Last Modified: 2014-01-16
Hi!
I need to calculate standart operating profit by dividing current year by prior year. Here is my formula:

=IFERROR(EG8/EN8-1,"") My issue is that if last year was negative and this year is positve, then the result is negative which is incorrect b/c there is a growth in the operating profit. How do I need to adjust the formula in order for my sign to flip to positive if last year data is negative.
For example: FY 2013 has $87,733 amd FY 2012 has -$43,267. The current answer is negative 302.8%  but it should be positive (87,733 +43,267)/43,267


Thanks!
0
Comment
Question by:Ladkisson
[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
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 12

Expert Comment

by:tel2
ID: 39787070
Hi Ladkisson,

Use the absolute value function: ABS(...)
That converts negatives to positives, but leaves positives (and zero) as is.
0
 

Author Comment

by:Ladkisson
ID: 39787083
ABS will not work...I've tried it...for example 2013 = $928,903
and 2012 =$1,083,156
SOP %= -14.2% which indicates the drop
If I use ABS it will turn into a positive 14.2%
0
 
LVL 12

Expert Comment

by:tel2
ID: 39787125
Oh.
What is the "EN8-1" part trying to do, in the =IFERROR(EG8/EN8-1,"") formula?  Why subtract 1?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:Flyster
ID: 39787184
This should work for the problem listed above. (Assuming 2013 is in column EG and 2012 is in EN)

=IFERROR((EG11-EN11)/EN11,"")

Flyster
0
 
LVL 43

Accepted Solution

by:
Rob earned 500 total points
ID: 39787321
tel2 - the formula for Gross Profit is:

([This Year] - [Last Year]) / [Last Year]

which can be expanded to

(TY / LY) - (LY / LY)

OR

(TY / LY) - 1

So that said, that formula can only be used for positive amounts.  When negatives are involved the formula has to go back to the unsimplified form:

=(TY-LY)/ABS(LY)
0
 
LVL 43

Expert Comment

by:Rob
ID: 39787326
Example attached
Gross-Profit.xlsx
0
 

Author Closing Comment

by:Ladkisson
ID: 39787634
Brilliant! So simple! thank you!!
0
 
LVL 12

Expert Comment

by:tel2
ID: 39787641
Nice work, Rob.

And good to see that my suggestion to use ABS() wasn't totally wrong.   8)
Too bad I didn't earn any points from it.
0
 
LVL 43

Expert Comment

by:Rob
ID: 39787652
Thanks tel2. Wasn't totally wrong, no. It just had to be in right place. Using abs on the whole formula would still yield incorrect results.
0
 
LVL 12

Expert Comment

by:tel2
ID: 39787657
Hi Rob,
I wasn't trying to imply that ABS() should be used on the whole formula, but I can see how it could have come across that way.  I was just trying to say that it would convert negatives to positives.  I didn't actually know how it should be used, exactly, and didn't want to spend the time or brain power finding out, and hoped that providing the name of the function was all that Ladkisson would need to solve it himself, but it seems that Ladkisson already knew about ABS(), and needed more than that.
0
 
LVL 43

Expert Comment

by:Rob
ID: 39787661
Oh ok, I know where you're coming from now :)
0
 
LVL 12

Expert Comment

by:tel2
ID: 39787669
I'm coming from just over the ditch, mate - NZ.
tel2
0
 
LVL 43

Expert Comment

by:Rob
ID: 39787674
Now that makes a lot of sense! :p
 j/k - I'm married to one so I can that ;)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 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