-1

I am trying to create a excel calculator, that will count time needed to complete a certain case. This is quite basic match but I might be wrong in my attempt.

Lets start with this.

We have a fixed time 15 minutes

Our task has to be completed at 6:40 minutes which will be a variable. Of course the timer is decreasing.

So after the time get to a point 6:40 minutes left it has to be 100% Our interval will be 30 seconds - Lets say its in column A.

15:00 0 %
14:30 xx,xx %
13:30 xx,xx %
12:30 xx,xx %
11:30 xx,xx %
10:30 xx,xx %
09:30 xx,xx %
08:30 xx,xx %
07:30 xx,xx %
06:30 99,?? %
05:30 Success
04:30 Success
03:30 Success
02:30 Success
01:30 Success
00:30 Success
00:00 Success

Basically I need a formula for column B that will count a %, that all will be based on the variable 6:40 which will be counter a % off..

If the explanation is not clear please tell me.

Online sheet https://docs.google.com/spreadsheets/d/1El-1WVgICJ5vrYs5vALYQ2kMrJZYqAk17Hvz5gXwFZs/edit#gid=0

3
  • 6m40s = 400s | 15m = 900s - So you have 500s to go from 100% to 0%, thus every 5s after 400s you lose 1%? Commented Jun 15, 2015 at 11:27
  • Essentially this means if your stop time is t you calculate what your success rate is by =100-((t-400s)/5)) right? Commented Jun 15, 2015 at 11:30
  • 900s is 0% variable t=6:40m and when it hits variable t its 100% the thing is to fill the % numbers between Commented Jun 15, 2015 at 11:35

1 Answer 1

1

Note that the sample you provided is actually showing hh:mm. That is not a huge problem so long as all things are consistently using the same format.

Assuming A2 is the max time, values decrease down the A column, and G6 is the target time. Using the sample spreadsheet you provided you can use the formula:

=IF(($A$2-A2)/($A$2-$G$6)<=1,($A$2-A2)/($A$2-$G$6),"Success")

Copy it down the column and it will adjust the references for each row.

At the core the calculation we're doing is:

($A$2-A2)/($A$2-$G$6)

Which is the max time less current time over max time less min time. Wrap that in an IF- function to return "Success" once equal to or greater than 100%

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .