Rookie Question

[Cameron J A]

Hi All,
I am after a formula that will calculate the percentage of two values and not exceed 100%.
For example, I have a planned value of 13 and achieve a actual value of 31, which returns a result of 238%. In this instance I would like the figure not to exceed 100%.

Any ideas?

Thank you in advance
Cameron

 

[esseispercipi4]

You can use MIN. If your actual value is in A2 and your planned value is in B2, use the following formula.

=MIN(1,A2/B2)

That will give you the lower value between 100% or the calculated percentage.

Hi All,
I am after a formula that will calculate the percentage of two values and not exceed 100%.
For example, I have a planned value of 13 and achieve a actual value of 31, which returns a result of 238%. In this instance I would like the figure not to exceed 100%.

Any ideas?

Thank you in advance
Cameron

 

[DeezNuts]

if you use =13/31 you get 42% this is how I would do the percent.
if you use =31/13 you get the 238%


I may not be getting what you mean though

 

[Cameron J A]

Thanks your answer was exactly what I was after.

You can use MIN. If your actual value is in A2 and your planned value is in B2, use the following formula.

=MIN(1,A2/B2)

That will give you the lower value between 100% or the calculated percentage.

 

[Cameron J A]

It's ok...esseispercipi4 answered it for me.
Thanks anyway DeezNuts

 

[Michael M]

I believe Williams answer would be quicker...