Calculate KPI using percentages

[AKAvenger]

Hi Gurus

I hope I can explain this correctly.
I need to calculate a KPI using %, I got as far as creating the target.

Column D is the target of 100%, so if the target is met then the achievement is 100%, I need to calculate the achievement with a max of 200%.
Any ideas on how to get this done?

 

[Candyman8019]

If I understand what you're trying to say, give this a try:
=IF(H7/G7>200%,200%,H7/G7)

 

[AKAvenger]

If I understand what you're trying to say, give this a try:
=IF(H7/G7>200%,200%,H7/G7)

Hi Candyman

In this KPI, less is better, so the actual % must be less than or = to the target. If the actual is = to target then 100% but if less then +100% etc.

 

[AKAvenger]

@Candyman8019
I have uploaded two screenshots showing my BEFORE and AFTER. I used excel goal seek to find the answers for my Min and Max columns but don't know how to create a formula that will work it out.
Does this help?

 

[AKAvenger]

If I understand what you're trying to say, give this a try:
=IF(H7/G7>200%,200%,H7/G7)

@Candyman8019 , I posted a detailed reply, did you see it?

 

[Candyman8019]

Okay, I've run some numbers and formulas...they work for most of the values in your sheet, but some are slightly off...I suspect due to the manually entered values possibly having some hidden decimal places? Anyway, give these a try:
Column G: =H2/0.7
Column I: =H2*0.5
Columh J: =D2/E2

 

[AKAvenger]

Okay, I've run some numbers and formulas...they work for most of the values in your sheet, but some are slightly off...I suspect due to the manually entered values possibly having some hidden decimal places? Anyway, give these a try:
Column G: =H2/0.7
Column I: =H2*0.5
Columh J: =D2/E2

@Candyman8019, you are my new hero, thank you! Numbers look good.