Find minimum of first four valid values

[Declamatory]

Hi Guys,

I'm creating a spreadsheet for a golf competition played over 8 weeks. The competitors can play up to four rounds at some point over the 8 weeks. Some players might play every week or play 6 weeks etc. but only their first 4 scores should count towards the competition. The other are rounds played for fun.

I have 8 values in 8 cells (not together in a range. say cell A3 in 8 separate sheets with sheet 1 being the scores for week 1 and sheet 8 being the scores for week 8) that contain values of the scores on hole 1. The cells may have a number for their score, the cell might say "Picked up" or may say "DNP" (did not play).

Can anybody tell me how I can find the minimum score the player achieved on hole 1 only looking at the first four valid scores the made over the 8 weeks ignoring where the player did not play.

Sorry if this isn't very clear.

Thanks very much.

[Glenn Kennedy]

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Declamatory]

Hi Glenn,

Thanks for your response. I did try to attach the file using the method you suggested but it just didn't upload.

[Declamatory]

The data in the dates tabs won't change but the master tab can be changed anyway to achieve the results of trying to find the lowest scores on each hole ultimately showing the best accumulative round of golf.

[Glenn Kennedy]

MIN-IF using non-contiguous cells is easy enough... I'm not sure abut how to limit it to the first n values...
This array formula will return the minimum value from those non-adjacent cells, ignoring any text...


=MIN(IF(CHOOSE(ROW(1:6),A1,C1,E1,G1,I1,K1)>0,CHOOSE(ROW(1:6),A1,C1,E1,G1,I1,K1)))

This formula was NOT tailored to your sheet. I'm thinking out loud, hoping someone else will know what to do next...

[Declamatory]

Thanks Glenn I've attached the sheet now. The dates data can't be changed (they are pasted in as the rounds are played from another program). The master sheet can be formatted in any way.

[Glenn Kennedy]

This is NOT finished. It requires more effort to get it right. However, it is done for holes 1&2. maybe someone else will spot a quick'n'easy way to do this. Maybe I'm just over-complicating it all.

What I need you to do is manually adjust the scores for some of the rounds for hole 1 and see if they behave EXACTLY as you need. Don't worry (too much) about the formulae (yet...). Focus on the outcome, not the method.

[Declamatory]

Hi Glenn,

Thanks for the time you have spent on this. I can see that the first players scores change on hole 1 but there are a number of players who don't have scores for the first hole yet. Is that because there is still stuff that needs doing?

Thanks

[Glenn Kennedy]

haha. that was caused by me being stupid and using a poor data layout. still only done for holes one and two... but at least it is working properly for holes one and two. Please check over... It'll not take much more to finish it, if it's OK.

[Declamatory]

Thanks Glenn, It looks great.

[Glenn Kennedy]

OK. Finished. i'd initially thought of using OFFSET to do the last bit, but it would have been grindingly slow. So, I went for a rather horrible looking INDEX formula, instead. I un-merged a few cells at the top of the Master sheet, to give me some room for working things out. Once its finished, feel free to merge them again, but until we agree that it's finished, can you leave them as they are?

I'm happy to explain any of the named ranges/formulae, etc, that I've used.

So, over to you. Try to break it.

There's an old Excel macro function in use to generate the list of sheets, so please remember to enable macros on opening.

[Declamatory]

Hi Glenn,

I'm afraid it came up with messages saying the file needed to be repaired when opening. All of the tabs were blank.

[Glenn Kennedy]

it downloaded and opened just fine for me. Try again??

[Declamatory]

Thanks Glenn, Top Bloke.

[Glenn Kennedy]

Great job! Thanks for the Rep.