Solved

Multiplication by negative value that should be positive

Posted on 2014-01-16
13
141 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
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 11

Expert Comment

by:tel2
Comment Utility
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
Comment Utility
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 11

Expert Comment

by:tel2
Comment Utility
Oh.
What is the "EN8-1" part trying to do, in the =IFERROR(EG8/EN8-1,"") formula?  Why subtract 1?
0
 
LVL 22

Expert Comment

by:Flyster
Comment Utility
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 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
Example attached
Gross-Profit.xlsx
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Closing Comment

by:Ladkisson
Comment Utility
Brilliant! So simple! thank you!!
0
 
LVL 11

Expert Comment

by:tel2
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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 11

Expert Comment

by:tel2
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
Oh ok, I know where you're coming from now :)
0
 
LVL 11

Expert Comment

by:tel2
Comment Utility
I'm coming from just over the ditch, mate - NZ.
tel2
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
Now that makes a lot of sense! :p
 j/k - I'm married to one so I can that ;)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now