Gladly.
First without referencing to sheet 41 so you can see how formula works:
If in A column you have value from A34 (Wich is ACT) then from B column take response time.
Since you don't have actually time there but text (as I wrote above it would be much easier if you enter time in B column) then you need to extract number that represent minutes.
There can be 1 min or 16 mins etc.
So we take 2 characters out by
- in first case 1 and space
- in second case 16
So we need to SUBSTITUTE space with nothing to get 1 and 16 (similliar for other numbers and that's the reason why is limitation to 99 because 3rd character for minutes above 99 will extract 1 m from 1 min and that is not number).
After you SUBSTITUTE space with nothing text (1 and 16 are still text at this point) need to be converted into number by -- (or some other method).
Since some cells will return error
(Like "Under a minute", 2 Left characters are "Un" converted into number return error)
then you need IFERROR formula to avoid them (Under a minute will be 0 after IFERROR formula).
From numbers that are left you will get MAX value.
Now, if you have times are times then all of this wouldn't be needed and you could just find MAX of them.
Bookmarks