how to get the weighted score out of the target score

[Mharvz]

Hi,

Our target is 75 seconds or less and it should be equivalent to 10% weight out of 100% on our scorecard.

So, if someone gets more than 75 seconds, the 10% weight will decrease.

Can someone please help me in the formula?

Thanks!

[David A Coop]

Sorry.

This is not very clear.

It's a good idea to post a copy of your workbook so that your requirements can be seen.

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)
Click Go Advanced (bottom right of your message window),
Unfortunately the Paper Clip doesn't work any more.
Scroll down until you see Manage Attachments (in blue text.
Click this text then click the Choose File button and find your file.
Click Upload. You can then click on the Close this window
You should now be returned to the Advanced window.
Click "Submit Reply"

Regards DAC

[Mharvz]

Hi,

I am currently using google drive so I cannot make a copy due to company security, attached is the screenshot though.

Thanks!

[David A Coop]

Sorry,

I can't help with the what you have provided. All I can see is that QA - 60% of 77.37 is 46.422 (which you art charting).
Same for Reliability - 30% of 77.168 is 23.1504.

Beyond that, I can't work out what you're asking for.

My apologies!

DAC

[Mharvz]

Hi,

Thanks for trying though.

My only problem here is the last part (Hold + Wrap Time) whereas; the target is 75 seconds or less and it should be 10% weight of the chart. I just couldn't seem to figure out how to convert seconds into percentage and to get the 10% weight of it.

Thanks!

[Glenn Kennedy]

We need more information. If the hold time is 70 seconds (inside target) the weighted score 10... or what? If the hold time is 80 seconds (outside target, is the weighted score 0, or something else. If something else, what is it and why is it that value.

=if(A1<=75,10,0) will assign 10 or zer0, by comparison with your target (adjust the A1 to suit, as we can't see which cell(s) you're using.

[Mharvz]

Hi,

If the target score (seconds) is greater than or equals to 0 and is less than or equals to 75 , the weight score should be 10%. If it is greater than or equals to 76 and is less than or equals to 1000 the weight score of 10% should decrease.

So, the higher the seconds (after 75 target is hit) the lower the weight percentage will be.

I am thinking of having the 75 seconds or less to be converted to 100% and just get the 10% of it as the weight (which is what I'm looking for). However, if the data is more than or equals to 76, the percentage should decrease and that is what I am having a problem with.

Thanks!

Best,

Mharvz

[Glenn Kennedy]

Try this:

=IF(A1<=75,10,10-0.4*(100-A1))

[Mharvz]

Hi,

You're the BEST!!!

Thank you!!!

Mharvz

[Mharvz]

Hi,

Sorry, it's a bit off. The data I have is 107.9 and after using the formula you asked me to try, it gave a weighted score of 13.16%. Whereas it should have been less than 10% because the data is more than 75.

Thanks!

Best,
Mharvz

[Mharvz]

I think I figure it out. It should be:

=IF(A1<=75,10,10-0.4*(100/A1))