What does this calculation do?!

[Paul (ESI)]

I am working with a colleague to create a spreadsheet to rank supervisor
performance through several aspects of the average performance of their
employees. We have been given a similar spreadsheet as an example of what is
needed. I am looking at it, and initially cannot understand what in the world
some of the calcuations are meant to do. Here is one that is meant to
calculate the "ranking score." It is a percentage determined by a target
percentage and the person's actual performance percentage:

=IF(IF(C3>0,(C3/B3),200)>200,200,IF(C3>0,(C3/B3),200))

As far as I can tell, the first inner if clause is saying, if C3's value is
greater than 0, it should divide C3 by B3 for the value, otherwise the value
is 200. Am I not right?

The next part is saying, if that entire value is greater than 200.... there
is were my confusion first comes in. How is the value going to be greater
than 200? It is either one percentage divided into another (this means it is
highly unlikely that the value will be over 200), or the value is assigned as
200, which is obviously not greater than 200.

The next part is somewhat confusing too. It seems to say that, if this is
the case (the entire value is greater than 200) the value now assigned IS
200. That just seems weird. The second possible if result (if the value is
less than 200) is another if clause which is exactly the same as the initial
inner if clause. What is this doing? If anybody can help, I'd great
appreciate it. I'm still trying to figure it out myself, but I'm stumped for
now.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

 

[Bob Phillips]

Paul,

It just seems to cap the result at 200, by testing this part
IF(C3>0,(C3/B3),200) to be greater than 200, and setting at 200. If not
greater than 200, re-do the calculation..

So if B3 = 1 and C3 = 700 say, the result is 200.

You could do it more simply with

=MIN(200,IF(C3>0,(C3/B3),200))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bernie Deitrick]

Paul,

A little simpler:

=IF(C4>0,MIN(C4/B4,200),200)

The next part is saying, if that entire value is greater than 200.... there
is were my confusion first comes in. How is the value going to be greater
than 200? It is either one percentage divided into another (this means it is
highly unlikely that the value will be over 200), or the value is assigned as
200, which is obviously not greater than 200.

If B4 is very close the zero, the ratio C4/B4 can easily be greater than 200, but this formula
limits it to 200.

HTH,
Bernie
MS Excel MVP

 

[Paul (ESI)]

OH! Wow! Thanks! I see now that I so close to getting it and I somehow could
not fill in the final blank. You filled it in for me. Thanks!

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

 

[Gary's Student]

If the numerator is greater than zero then use the division else use 200.
If the result of the above is greater than 200 then use 200.

 

[Paul (ESI)]

Thanks to you both as well. I may suggest to my colleague that we try one of
your suggestions instead of doing what this other person did with their
spreadsheet. Once I figured out what the calculation did (thanks, in part, to
Gary's Student's assistance. Thanks again!) it did seem more complicated than
it had to be to perform what it was doing.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy