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.
--
Gary's Student


"Paul (ESI)" wrote:

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(C30,(C3/B3),200)200,200,IF(C30,(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