# calculate target to plan

#### Lizzi

##### Board Regular
Hi,

im trying to calculate how close to target the sales team are
in col C i have their plan, in col D i have what they have made so far and have this formula in col F =(C4-D4)/C4
it shows me how far in % they are to the plan but if someone has over acheived it gives me a negative %
but that wouldnt be right has they have gone beyond the plan.

col C 764,403 col d 679,334 col f 11% so they are 11% away from reaching their target

<tbody>
</tbody><colgroup><col></colgroup>

any ideas plse?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
What do you want it to show when it goes above 100% to goal?

You could do % of goal rather than % to goal, such as:

=D1/C1

Then you would avoid any negatives.

Book1
CDEF
11764403800000-4,66%
Cell Formulas
RangeFormula
F11=(C11-D11)/C11

What would be the result if D > C?

The negative value (%) shows the result is achieved.

i want it to show whatever it is in %, some has over acheived and it shows -148% but that is misleading, the 148% is good, just without the -

if you don't add the - you will not know if there is a target to go (or the target is already achieved).

So the - is very usefull in this case (to my opinion)

i want it to show whatever it is in %, some has over acheived and it shows -148% but that is misleading, the 148% is good, just without the -

Maybe:

=IF(D1 < C1,(D1-C1)/C1,D1/C1)<c1,(d1-c1) c1,d1="" c1)<="" html=""></c1,(d1-c1)>

Thanku that works well

Maybe:

=IF(D1 < C1,(D1-C1)/C1,D1/C1)<c1,(d1-c1) html="" c1)<="" c1,d1=""></c1,(d1-c1)>

Replies
0
Views
273
Replies
25
Views
2K
Replies
11
Views
286
Replies
3
Views
483
Replies
25
Views
503

1,219,905
Messages
6,150,901
Members
450,991
Latest member
ExcelDoer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back