Time Comparisons

  • Thread starter Thread starter TARogers
  • Start date Start date
T

TARogers

I am having some problem comparing 2 time values. When I compare 28:0
(mm:ss)(H9) to 28:12 (H10) (formula: if(H10>H9, "Pass", 'Fail"), I ge
"Fail". But when I do the same thing using lookup, I get "Pass"
if(H2>(Lookup(Sheet3!A2,Minimums!A2:A35,Minimums!D2:D35)),"Fail","Pass")
Sheet3!A2 is age of the person
Minimums!D2:D35 is time based on age, A2:A35

I also have a formula that displays"Fail" if any one event display
"Fail"
if(or(c2="Fail",F2="Fail,I2="Fail"),"Fail","Pass"). I am not sure i
this is working because the above displays "Pass" regardless of th
time entered. All help is appreciated.

Tod
 
if(H2>(Lookup(Sheet3!A2,Minimums!A2:A35,Minimums!D2:D35)),"Fail","Pass")

Just wondering whether the above IF should be instead:

if(H2>(Lookup(Sheet3!A2,Minimums!A2:A35,Minimums!D2:D35)),"Pass","Fail)

(so as to be "equivalent" to the other one one mentioned... IF(H10>H9,
"Pass", 'Fail")...)

Also presume that the values in Minimums!A2:A35 are placed in ascending
order?

---------------------------
if(or(c2="Fail",F2="Fail,I2="Fail"),"Fail","Pass")
Click to expand...

Other than a missing closing double quote in the above formula:
F2="Fail should be F2="Fail"
(probably a typo in your post)

the formula should work correctly, ie it will return "Fail"
if any of the 3 cells C2, F2 or I2 contain the phrase "Fail", otherwise it
will return "Pass"

The IF formula has no "time entered" as inputs, it only checks for the
phrase "Fail"
in C2, F2 or I2 before returning the result

-
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------
 
I changed the formula to "Pass", "Fail", which works one time. As
enter times that are above or below the max time allowed, the resul
does not switch (i.e. If max time is 28:00, and I enter 29:12, I get
"Fail", but if I switch the time to 25:23, I still get a "Fail")

When I just use a simplified formula comparing 28:00 to 26:23 and 28:0
to 29:12, the formula works fine. I only have problemn when I use th
lookup function. Please help. Thanks
 
In sheet: Minimums

do these corrections to the run-times in col D

click on D2 > copy
select D3:D11 > paste

click on D12 > copy
select D13:D24 > paste

click on D25 > copy
select D26:D30 > paste

click on D31 > copy
select D32:D35 > paste

(some data in col D contained errors
eg: in D3:D11, in D13:D24, etc)

------------------------------
In sheet: Scoresheet

Change the formula in I2 to:
=LOOKUP(A2,Minimums!$A$2:A35,Minimums!$D$2:$D$35)

where the reference ranges are *locked*,
ie with absolute cell refs
eg: Minimums!$A$2:A35, Minimums!$D$2:$D$35

before you copy down the column

otherwise the ranges will change relatively
when you copy down, which is wrong

Likewise, lock the ref ranges in the LOOKUP formulae
in the other hidden cols C & F before you copy down the cols

Now, everything should work fine...
 
so Todd,
how did the suggested fixes go?
pl feedback...

Rgds,
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
 
Back
Top