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
Matt PinkstonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Phillip BurtonConnect With a Mentor Director, 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.