Home |
Search |
Today's Posts |
#1
|
|||
|
|||
What does this calculation do?!
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 |
#2
|
|||
|
|||
Paul,
It just seems to cap the result at 200, by testing this part IF(C30,(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(C30,(C3/B3),200)) -- HTH RP (remove nothere from the email address if mailing direct) "Paul (ESI)" wrote in message ... 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 |
#3
|
|||
|
|||
Paul,
A little simpler: =IF(C40,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)" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote: 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
time-clock calculation | Excel Worksheet Functions |