Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Need help with an Excel calculation

Posted on 2015-02-11
Medium Priority
61 Views
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
Question by:Matt Pinkston
[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
• 3
• 2

LVL 24

Expert Comment

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

If zero, then

max(0, availablesales / gap)
0

Author Comment

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

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

ID: 40603555
so how does that formula look?
0

LVL 24

Accepted Solution

Phillip Burton earned 2000 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

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month9 days, 15 hours left to enroll