Formula to count MIN of column with a condition

[RunHard]

Hello again,

I have a formula that tests the minimum time in a column. If the time is the minimum it gets a PB (Personal Best) notation. My problem is once a season there is a competion that needs to be recorded on the worksheet however I do not want this time for this competion counted in the MIN test or to get a PB allocated. I have worked out how to ignore the PB but I cannot get the MIN test to work properly. Dates of comp are in Col B, data (times) in Col C and formula for PB's in Col D. When a cell in Col B (Dates of comp) = Region I want the MIN test to ignore the value in the adjacent cell in Col C.

Please Login or Register  to view this content.

This formula has been filled down from D10 to D29. Region (condition) date can be anywhere in B10 to B29.

Any suggestions.

Thanks

RunHard

[JBeaucaire]

That's a lot of hard to picture explanation...couldn't a workbook show us this much more clearly?

Post one up and highlight what you want, show the desired result and we'll try adjusting the raw data with real formulas to accommodate.

[RunHard]

Will do. Its been a while so I will have to refresh on how to post a worksheet.

Bolter

[royUK]

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

Moved to Excel Functions

[JBeaucaire]

Go advanced > paperclip icon.

[RunHard]

Sheet attached sample of the worksheet.

So what I am after is for the formula to ignore data in C15 (Adjacent to B15 = "REGION". This formula should then recognise C16 as a PB (Next best time other than REGION day). The region competition day date changes every season so it will not always be in B15. The formula I am after will somehow recognise "Region" in Col B and ignore data in the cell adjacent in Col C.


RunHard

[JBeaucaire]

Now show us the same chart with the answers you're looking for, I still have no idea what you're asking for, where, even with all the "how" you've offered. Simple Before/After is so much clearer.

[DonkeyOte]

If D10 were to equal "PB" which I'm guessing it should do then you could use:

D11:
=IF(OR(B11="REGION",C11=""),"",LOOKUP(REPT("z",255),CHOOSE({1,2},"",REPT("PB",C11<LOOKUP(2,1/(D$10:D10="PB"),C$10:C10)))))
copied down as required.

Others may suggest a MIN(IF( array... which given volume of data may not be too bad...but I would still opt for above type approach myself.

[JBeaucaire]

Also, we can simplify your existing formula a wee bit, too. In D11 and copied down:

=IF(AND(B11<>"REGION",ISNUMBER(C11)),IF(C11<(MIN(C$10:C10)),"PB",""),"")

[DonkeyOte]

JB, the formula in D is the one that needs to be edited I believe to account for the fact that time accompanying "REGION" to be discounted when establishing PB's... for this a MIN(IF array could be used but I based my non-array on the fact that you don't need to find the MIN technically... all you need do is find the last instance of PB in the preceding rows as this is the current PB (ie the MIN value)

[JBeaucaire]

Yeah, I don't follow any of that at all. Your formula just gives me FALSE all the way down the column D when I use it, too. I'd like to hear from the OP exactly what the answers should look like.

[RunHard]

Thanks guys for the reply.

JB, attached is the result I am after in the worksheet. C16 is where I want the formula to recognise it as a PB ignoring C15 even though it is a better time.

DonkeyOte, I tried that formula and it did not pick up any PB's when I entered data. C10 is the first comp time for the season and anything after this which is a better time will need to be recognised be a PB with the exception of Region day.

Thanks for your efforts.

RunHard

[DonkeyOte]

JB, not quite sure how you've managed to get it to return FALSE given if inserted correctly it would only ever return a Null or "PB". As I mentioned all would be blank unless D10 (first time) were assigned as "PB" manually (or via formula).

I've attached a sample to show in operation to avoid further confusion for OP.

[RunHard]

DonkeyOte,

I see what you are saying and it didn't work on my worksheet due to C10 not being allocated a PB. As mentioned in a previous post the first time of the season is not a PB, everything after that, that is a better time is a PB. An athlete might not compete in the first comp day therefore this would throw out the formula. His first comp day might be C13 and then anything better after this would be a PB. Hope I am making sense.


RunHard

[DonkeyOte]

You could as I say use an array

Please Login or Register  to view this content.

[RunHard]

DonkeyOte,

Thank you very much, your last suggestion works perfectly. Last piece of the puzzle.

Very Grateful

RunHard