• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match Small

Greenbriars

Greenbriars

Member
My head is spinning and I need a little help.

I have been asked to provide a spreadsheet to provide real time information for an 8 hour dinghy race. There will be up to 24 competitors and up to 32 laps. The spreadsheet will be run in LibreCalc on a Raspberry Pi and because the keyboard is miniscule actual times will be input as mmmss with no punctuation.

Typically, as the project has progressed there have been more "wouldn't it be great if it could do....." requests. I thought it was finished when I was asked to provide the fastest and slowest times per hour which I achieved with MINIFS and MAXIFS. Now I am being asked to provide the 3 fastest and slowest times per hour. I'm pretty sure that I should be able to do the former using INDEX MATCH and SMALL but can't get my head around it. I'm not sure how to do the latter.

I attach a page from my current spreadsheet showing how I have arrived at the fastest and slowest times per hour. Any help to provide fastest 3 and slowest 3 would be greatly appreciated. I hope that LibreCalc converts to Excel properly.

Regards,

Alan
 

Attachments

  • Chandoo.xlsx
    35.5 KB · Views: 8
How do you want to see your result organized? Can you mock up expected result and layout for some?

It will make it much easier for us to provide you with right solution.

If I understood it right, you don't need Index,Match & Small, but just Small(IF()) construct.
 
Thanks for your prompt reply.

In the attached spreadsheet cell E20 should contain the second smallest number in row 9 where the number in row 11 is 1. Likewise F20 should contain the third smallest number where row 11 is 1 and so on. Cell E33 and cell F33 should contain the second largest number in row 9 where row 11 is 1 and so on.

Hope this makes sense to you.

Regards,

Alan
 

Attachments

  • Chandoo1.xlsx
    35.7 KB · Views: 6
Ok... first thing you want to do is to get rid of all those #Value! in Row 11.

Use IFERROR(your formula, "").

Then update your formula in D20 to...
=IFERROR(SMALL(IF($C$11:$ACP$11=$B20,$C$9:$ACP$9),COLUMNS($A$1:A$1)),0)

Confirmed as array (CTRL + SHIFT + ENTER)

For D33
=IFERROR(LARGE(IF($C$11:$ACP$11=$B33,$C$9:$ACP$9),COLUMNS($A$1:A$1)),0)

Confirmed as array (CTRL + SHIFT + ENTER)

Copy both across and down.

See attached.
 

Attachments

  • Chandoo1.xlsx
    37.4 KB · Views: 7
Does this formula do the same?

=MIN(IF($C$11:$ACP$11=$B20,$C$9:$ACP$9)) for D20
=MAX(IF($C$11:$ACP$11=$B20,$C$9:$ACP$9)) for D33

Array formula confirm with (CTRL + SHIFT + ENTER)

Thanks
 
Nebu

Your formulae correctly the fastest and slowest laps per hour but they do not determine the second and third fastest or slowest. Chihiro's formulae work perfectly and have saved me a great deal of time and frustration.

Thank you for showing an interest.

Regards,

Alan
 
Hi Alan and all,
Good day...

If you have 2010+ you can use Aggregate function with just enter.

in D20:
=IFERROR(AGGREGATE(15,6,1/($C$11:$ACP$11=$B20)*$C$9:$ACP$9,COLUMNS($A$1:A$1)),0)

in D33:
=IFERROR(AGGREGATE(14,6,1/($C$11:$ACP$11=$B33)*$C$9:$ACP$9,COLUMNS($A$1:A$1)),0)

copy down and across...

Regards,
 
Khalid

I didn't even think of using AGGREGATE. I am so grateful to you for posting this. I haven't had an opportunity to try it yet, but it an elegant solution to the problem.

Excel is just so full of surprises. I'm frequently surprised at how many ways one can find to achieve an objective. As they say "There's more than one way to skin a cat".

Thank you so much for your interest.

Best regards,

Alan
 
You have all been very helpful.

As I said, there is almost always a number of ways of solving an Excel problem.

I may have made a fundamental error in my thinking about how best to achieve what I wanted. I will be recording time information in a 32 * 24 matrix. If I could have performed the hourly fastest and slowest data calculations from the same matrix, then the spreadsheet would be more easily scalable.

My thinking was that I needed to have all the time data on the same row, but I may have made an error. Is there a way of manipulating the data in the attached spreadsheet to achieve my objective? The time input data are in H5:AM28 and the format is mmmss with no punctuation. The lap data are in H121:AM144. The output is in rows 150 onwards.

If anyone can achieve this it will make my life easier and improve my understanding of Excel functions.

Thanking you so much in anticipation.

Regards,

Alan
 

Attachments

  • Chandoo 3.xlsx
    69.2 KB · Views: 8
What I would recommend is to start from flat table structure instead of cross-tab table. While, cross-tab may be more readable to human eyes, it isn't well suited to analysis via formula/program or other tools.

Starting from flat table simplifies data analysis downstream and allows use of pivot tables (which is very powerful and easy to use tool for data analysis).
 
Chihiro

Thanks for your continued interest. I understand exactly what you say about using a flat table structure rather than a cross-tab structure.

However, my main concern here was to be the economy of data entry. I will be using a TINY keyboard and my objective was to be able to enter 4 or 5 digits and press an arrow key to get to the next data entry point. I still think that the cross-tab structure is best for this. That is unless someone can convince me otherwise.

My little project may not be the most sophisticated spreadsheet model but I know already that it will provide excellent data. I am very happy with what my little Raspberry Pi and Libre Office can display. Minimal data entry, export .png images and run an Impress slideshow. Easy!

Very many thanks for your excellent help.

Best regards,

Alan
 
You must log in or register to reply here.
Back
Top