I am trying to create a formula to represent OTD (%) on the following values: M5 = Date Returned (Actual)

L5 = Promised Return Date

S5 = Variance (in Days)If the Date Returned (Actual) equals the Promised Return Date then OTD is 100% but if the Date Returned is greater than the Promise Return Date by 1 day then 10% is subtracted per each day late. I've created the following formula using only the Variance but if I format the column with percentages excel is multiplying each cell by 100 and I am curious to see what the formula would look like using M5 and L5.

=IF(S5<=0,100,100-S5*10)

Well, it seems to me that if the final result is being multipliedby some factor when the % formatting is applied, it would make sense todivideeach number in the formula by the same factor.Or you could just use percentage signs right in the formula.

Give those a shot and let us know how they work for you.

P.S. What happens if the item is returned 11 days late?

Youâ€™re a genius, again thank you!!!!

I forgot...if returned 11 days the result is zero. I'm using a scorecard based on 1-10.

Ask Your Question

Weekly Poll