calculate target to plan

[Lizzi]

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?
thanx in advance for your help

 

[bbott]

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.

 

[Oeldere]

Book1
	C	D	E	F
11	764403	800000		-4,66%
Blad1

Cell Formulas
Range	Formula
F11	=(C11-D11)/C11

What would be the result if D > C?

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

 

[Lizzi]

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 -

 

[Oeldere]

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)

 

[bbott]

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)>

 

[Lizzi]

Thanku that works well

Maybe:

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

 

[bbott]

Thanku that works well

You're welcome.