How to Determine needed adjustments to reach Target Percentage

[HypnoFant]

Good evening,

I am currently working on a file for a call center I work in and am having a hard time figuring out a particular formula and making it work. Basically there is a monthly goal for a disconnect rate and we want this to be as low as possible. On a daily basis we have agents enter in their numbers into a shared sheet to give us a running track for the day. I'm currently trying to modify the sheet so that we can determine how many additional calls would be needed to reach the goal. The problem I am experiencing is the number of various formulas I've tried give very incorrect numbers and I'm not certain what is wrong. Here is the example numbers.

So I have attempted a variation on this formula =IF(C1>D1, (ROUNDUP(((A1*D1-B1)),0)), "PASSED") however it only provides the "passed" response. (obviously not the answer as I'm pulling examples from other posts on the forum). I've also attempted a variation on =ROUNDUP((D2*A2-B2)/(1-D2),0) however I get a response of -7. These appear to be the more common types of formulas. I know the answer on this should be 25 to reach the 20% target number. These numbers will fluctuate throughout the day as more disconnects and calls are taken by the agents.

Are there any ideas or suggestions you may have to help clarify what I'm doing wrong? Thank you for your time and expertise.

 

[Burrgogi]

Try this:
=ROUNDUP(($B$2/$D$2)-$A$2, 1)

I can't see the row numbers in your screenshot. I'm going to assume that you have column headers in row 1 and your data starts at row 2.

 

[Sanjeev1976]

You can try :

=IF(C2>D2,ROUNDUP((B2/D2)-A2, 1),"")

 

[HypnoFant]

Thank you both @Burrgogi and @Sanjeev1976 !!! Sanjeev's solution fits better for what we need with the extra IF requirement. If I may ask, what purpose does the $ do within the excel logic structure?

 

[Burrgogi]

If I may ask, what purpose does the $ do within the excel logic structure?

They are called absolute cell references when used in a formula. It tells Excel to always refer to a particular cell(s), even if the cell containing the formula is moved/copied elsewhere. They are not necessary in your case but I included them just in case you decided to move your formula to another cell. Upon further thought, I probably should not have done that. If you copy the formula that Sanjeev gave you to another spot on the worksheet, say from F2 to F6 you'll see that Excel will automatically adjust the formula for you by 4 rows. This is Excel's way of "helping" the user. It's particularly helpful if you happen to have the same or similar group of data further down on the worksheet or if you move the original data elsewhere. Now copy & paste the formula that I gave you to another spot on the worksheet and you'll notice the difference right away.

To use absolute or mixed references in your formula, press F4 while you're typing the formula. For example, type: =SUM(A2, then press F4 right after you've typed in the cell address. You can toggle through that and see what it does.

Further explanation here:

Absolute Reference in Excel

An absolute reference in Excel refers to a fixed location on a worksheet. When you copy a formula, an absolute reference never changes.

www.excel-easy.com