• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • 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 BurtonDirector, Practice Manager and Computing ConsultantCommented:
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 BurtonDirector, Practice Manager and Computing ConsultantCommented:
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 BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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