Formula for Inverted Ratio

[StephenB]

Hello, I have a user that needs to measure timeliness based on number of days where 30 days = 100%. For example, if the number of days is 0, then score is 200%, days 15 then 150%, days 30 then 100%, days 45 then 50%, days 60 then 0%. Assuming # of days is in A1 and Score is in A2.
I'm stumped due to that fact that it's an inverted ratio, as the number of days go up, the score goes down.
Any ideas and TIA for any assitance.

 

[qafself]

Hi Stephen,

Try this as attached - it works for me!

Ed

 

[unmarkedhelicopter]

This is not a logical formula you are looking for here as if I take 90 days then is my timeliness -50% ?
Is 'normal' time ALWAYS 30 days ?
Will 0 days always be 200%, 30 days 100% and 60 days 0% ?

If so then =(60-A1)/30 where A1 is your number of days

 

[unmarkedhelicopter]

Hi Stephen,

Try this as attached - it works for me!

Ed

What attached ?

EDIT:
Ah I see it now !

 

[qafself]

And it has logic!

Ed

 

[unmarkedhelicopter]

Logic ?
Explain it to me then, what makes 60 (i.e. double the time) worth 0 ? This is purely arbitrary.
If normal is 100% and 0 is 200% then 60 should be 50%, 90 33% etc.

 

[qafself]

It may be arbitary but it's the rule. The time span is predetermined and has limits and the formula will work with any time span. The rule may be arbitary but the logic of the formula overcomes it.

I don't make the rules, I just overcome them!

Ed

 

[StephenB]

qafself: Thanks! After quick testing, it looks like it works. I see how you're referecing 60 days as zero %. However, this formula will be added to an existing report, so the "Days" colomn may not even contain a cell with 60 days let along 60 days in a fixed cell. However, I can add that (and then hide it.)
Also, I neglected to mention (sorry): anything over 60 would be 0%.

Unmarked helicopter; rather than think of 30 days as normal therefore being 100%, think of it more in terms of ranges: 0-60 days and 0%-200%. Again, anything over 60 days would be 0%.

 

[unmarkedhelicopter]

You are correct, the rules were set by the poster.
But financially it makes no sense as the timliness of (say) a loan repayment would have financial implications which can be calculated as a rate of return modified by this factor. The calculated factor above has but little resemblance to the real world.

 

[unmarkedhelicopter]

qafself: Thanks! After quick testing, it looks like it works. I see how you're referecing 60 days as zero %. However, this formula will be added to an existing report, so the "Days" colomn may not even contain a cell with 60 days let along 60 days in a fixed cell. However, I can add that (and then hide it.)
Also, I neglected to mention (sorry): anything over 60 would be 0%.

Unmarked helicopter; rather than think of 30 days as normal therefore being 100%, think of it more in terms of ranges: 0-60 days and 0%-200%. Again, anything over 60 days would be 0%.

Then you'd want =IF(A1>60,0,(60-A1)/30)

 

[StephenB]

Thank you gentlemen, I agree that it doesn't make sense, but the user is happy!

 

[qafself]

Good to hear that you have a solution that serves your purpose - thanks for the feedback.

Ed