[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

Multiplication by negative value that should be positive

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
Ladkisson
Asked:
Ladkisson
  • 5
  • 5
  • 2
  • +1
1 Solution
 
tel2Commented:
Hi Ladkisson,

Use the absolute value function: ABS(...)
That converts negatives to positives, but leaves positives (and zero) as is.
0
 
LadkissonAuthor Commented:
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
 
tel2Commented:
Oh.
What is the "EN8-1" part trying to do, in the =IFERROR(EG8/EN8-1,"") formula?  Why subtract 1?
0
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!

 
FlysterCommented:
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
 
RobOwner (Aidellio)Commented:
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
 
RobOwner (Aidellio)Commented:
Example attached
Gross-Profit.xlsx
0
 
LadkissonAuthor Commented:
Brilliant! So simple! thank you!!
0
 
tel2Commented:
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
 
RobOwner (Aidellio)Commented:
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
 
tel2Commented:
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
 
RobOwner (Aidellio)Commented:
Oh ok, I know where you're coming from now :)
0
 
tel2Commented:
I'm coming from just over the ditch, mate - NZ.
tel2
0
 
RobOwner (Aidellio)Commented:
Now that makes a lot of sense! :p
 j/k - I'm married to one so I can that ;)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now