We help IT Professionals succeed at work.

Excel Formula help: getting a coverage value

Matt Pinkston
on
I have an excel spreadsheet that does a bunch of calculations and I need help understanding how to perform a specific formula.

sample 1
target e5 = $467,000,000.00
sold e8 = $154,134,335.36
gap e8 - e5 = -$312,865,664.64  
pipeline = $202,265,246.67
coverage is what percentage of the gap is covered by the pipeline, what formula will get me that accurate %

Keep in mind that sometimes the gap could be a positive number if that group has made their numbers.
Comment
Watch Question

Assistant Vice President\Network Manager
Commented:
So if gap is positive then pipeline isn't used?

E5 = $467,000,000.00
E8 = $154,134,335.36
E10 = E5 - E8  $312,865,664.64
E11 = $202,265,246.67

E12 = formula

formula =IF(E10>0,0,((E11/E10)*-1))

if E10 is greater than "0" then none of pipeline is used ("0") otherwise divide pipeline by gap and multiply by "-1" (to make it positive) to get percent gap is covered by pipeline.  

Is that what you are after?

Almost forgot - I get 64.65% using these figures.