Solved

Need help with an Excel calculation

Posted on 2015-02-11
5
58 Views
Last Modified: 2015-03-06
I need a calculation that would tell me

we have a target number a sold number and we get gap by subtracting sold from target

we also have an available sales  number which are sales that we think we will have

what we need a calculation for the coverage percentage which is the percentage of available sales to gap

example...
gap = 100,000
available sales = 200, 000

so the coverage would be 2* or 200%
 
but sometimes when sold already exceeds target you could end up with a negative number

example
gap = -50,000
available sales = 100,000

I want to understand the best calculation to depict the coverage
0
Comment
Question by:Matt Pinkston
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40603012
And what answer do you want in the second example?

If zero, then

max(0, availablesales / gap)
0
 

Author Comment

by:Matt Pinkston
ID: 40603020
actually the gap would be very high because your GAP is a negative number and your available sales is higher than that

so maybe I am wrong but if

Gap = -50,000
Available Sales = 100,000

the coverage should be 1.5 or 150% right
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40603027
So gap = 100,000, available sales = 200, 000, answer = 2.
Gap = -50,000, available sales = 100, 000, answer =1.5

Really?

If so, then

If(Gap<0,1-Gap/AvailableSales,AvailableSales/Gap)
0
 

Author Comment

by:Matt Pinkston
ID: 40603555
so how does that formula look?
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40603562
If Gap is in column B, and AvailableSales in Column C, then cell D2 would be:

If(B2<0,1-B2/C2,C2/B2)
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

740 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