• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scorecard Help!

Yandeez

Yandeez

Member
Hi,

Please see attached. The first example (blue) contains a sample scorecard. As you can see the increase in target doesn't correspond to the actual increase in $. The 125% target is not 125% of $10,000,000. As a result, it is very difficult to determine a YTD score and a long, difficult, custom calculation is used. For example, =IF(AND(H9>G3,H9<=H3),0,IF(AND(H9>=G4,H9<=H4),((H9-G4)/(H4-G4)*(F4-E4)+E4),IF(AND(H9>=G5,H9<=H5),((H9-G5)/(H5-G5)*(F5-E5)+E5),IF(AND(H9>=G6,H9<=H6),((H9-G6)/(H6-G6)*(F6-E6))+E6,IF(AND(H9>=G7,H9<=H7),((H9-G7)/(H7-G7)*(F7-E7)+E7)))))). This example formula will reference some sort of grid as seen in the spreadsheet.

The second example is completely linear so it's much easier to determine a score simply by taking the YTD result/100% target. See cell I6.

Management likes to set their own targets based upon a # of external factors so we can't use the 2nd method to determine a score. If we use the first scorecard, aside from building a custom calculation, is there an alternative method to determining a score? This is causing a world of confusion for everyone since the custom calc was built by one person and it's not easy to see why, $12,000,000 is 125% for example.
 

Attachments

Paul, thanks for the input but that simply finds the result if it is exactly the target. What if the YTD result is $11,500,000 then the index match returns N/A. I'm afraid that will not work.
 
Sorry... You need to reverse the order of your numbers... Lowest on the left... and the results have to be larger that the smallest number... See attachment... I only changed the top set...
 

Attachments

Yandeez... See attachment to explain the match and index so you can avoid all that crazy math you have up top...

I put three columns in for you... Understand the match function in the 1st column... Then look at the index column to see how we get the corresponding %... Then see in the 3rd column how to put them together...

I hope this helps...

PaulF
 

Attachments

Paul! Thank you for all your help. Please see your file attached. Using this example, Bob did $441. I see how that is 40% but shouldn't that be somewhere between 40% and 50%?
 

Attachments

You must log in or register to reply here.
Back
Top