Go Premium for a chance to win a PS4. Enter to Win

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

Need help with an Excel calculation

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
Matt Pinkston
Asked:
Matt Pinkston
  • 3
  • 2
1 Solution
 
Phillip BurtonCommented:
And what answer do you want in the second example?

If zero, then

max(0, availablesales / gap)
0
 
Matt PinkstonAuthor Commented:
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
 
Phillip BurtonCommented:
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
 
Matt PinkstonAuthor Commented:
so how does that formula look?
0
 
Phillip BurtonCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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