Target Calculation formula

[theraindew]

Below is a sample table i'm working on. We have an opening number for each member in B and a target closing number in C. Column D is the actual closing number. e.g. Mem5 target is 17,000 and closing is 17,000, Column E should show 100%.
Mem4 target is 18,400, but opening and closing are same at 92,000, column E should show 0%.
I just cannot wrap my head around how to achieve this. Appreciate any help on this.

Thank you

Excel 2013/2016

	A	B	C	D	E

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Opening Number[/TD]
[TD="align: center"]Target[/TD]
[TD="align: center"]Closing Number[/TD]
[TD="align: center"]Target achieved %[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Mem1[/TD]
[TD="align: center"] 90,000[/TD]
[TD="align: center"] 18,000[/TD]
[TD="align: center"] 75,000[/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Mem2[/TD]
[TD="align: center"] 75,000[/TD]
[TD="align: center"] 15,000[/TD]
[TD="align: center"] 72,000[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Mem3[/TD]
[TD="align: center"] 80,000[/TD]
[TD="align: center"] 16,000[/TD]
[TD="align: center"] 40,000[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Mem4[/TD]
[TD="align: center"] 92,000[/TD]
[TD="align: center"] 18,400[/TD]
[TD="align: center"] 92,000[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Mem5[/TD]
[TD="align: center"] 85,000[/TD]
[TD="align: center"] 17,000[/TD]
[TD="align: center"] 17,000[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Mem6[/TD]
[TD="align: center"] 97,000[/TD]
[TD="align: center"] 19,400[/TD]
[TD="align: center"] 25,000[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]Mem7[/TD]
[TD="align: center"] 76,000[/TD]
[TD="align: center"] 15,200[/TD]
[TD="align: center"] 76,000[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]Mem8[/TD]
[TD="align: center"] 91,000[/TD]
[TD="align: center"] 18,200[/TD]
[TD="align: center"] 36,000[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Mem9[/TD]
[TD="align: center"] 87,000[/TD]
[TD="align: center"] 17,400[/TD]
[TD="align: center"] 17,400[/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Mem10[/TD]
[TD="align: center"] 89,000[/TD]
[TD="align: center"] 17,800[/TD]
[TD="align: center"] 50,000[/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1​

 

[Scott T]

Try

=IF(C2=D2,100%,IF(D2=B2,0%,C2/D2))

 

[AlanY]

am i right to assume that based on your sample data all are at 0% with the exception of Mem5 & 10 at 100%?

 

[theraindew]

Thanks. This seems to work!

Excel 2010

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Opening Number[/TD]
[TD="align: center"]Target[/TD]
[TD="align: center"]Closing Number[/TD]
[TD="align: center"]Target achieved %[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"]0%[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"] 9,000 [/TD]
[TD="align: center"]22%[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"] 8,000 [/TD]
[TD="align: center"]25%[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"] 7,000 [/TD]
[TD="align: center"]29%[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"] 6,000 [/TD]
[TD="align: center"]33%[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"] 5,000 [/TD]
[TD="align: center"]40%[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"] 4,000 [/TD]
[TD="align: center"]50%[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"] 3,000 [/TD]
[TD="align: center"]67%[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"]100%[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"] 10,000 [/TD]
[TD="align: center"] 2,000 [/TD]
[TD="align: center"] 1,000 [/TD]
[TD="align: center"]200%[/TD]

</tbody>

Sheet1​

 

[theraindew]

Try

=IF(C2=D2,100%,IF(D2=B2,0%,C2/D2))

Thanks. This seems to work!

 

[theraindew]

am i right to assume that based on your sample data all are at 0% with the exception of Mem5 & 10 at 100%?

No, rest of them have reduced the number by some margin, even though they haven't achieved the target. Achieved % cannot be 0% for anyone except Mem 4 & 7. Their numbers have not reduced by any margin.