Automatically fill in results tables

I'm struggling to come up with a formula and even what functions to use for the following problem. I generally sort the data myself and copy and paste, but it would be nice to figure out how to get it to work automatically.

In the attached spreadsheet, I have a data set on the left, and 4 results boxes (titled with what should go in them).

For example, the cell H6 should return the value/name in B3 as the smallest male time. Likewise L6 should also return the value in B3 with the highest male %. And so on for the other results boxes.

Is it also possible if I could start with a blank data set, and entered in B3 the name 'ABC', and corresponding time in C3 of 1:00:00, for it to end up in H6. Only then to enter a new name in B4 of 'XYZ' and a time in C4 of 0:30:00, for THE value in H6 to change to 'XYZ' (as the new smallest time) and the value 'ABC' to be shunted down into cell H7 (as the 2nd smallest time).

Apologies if the above isn't worded very well.

Thanks in advance

I'd suggest Pivot Tables

automatically-fill-in-results-tables-Pivot.xlsx

Thanks. I will try both methods and see what works best for me. I hadn't even thought of pivot tables.

You're welcome.

I've marked this as unsolved as I've just encountered a problem with the array formula.

If I expand the data area (i.e. make it larger to include blank fields that have yet to be filled in), then the formula returns an error as it's including the E column values that don't exist (I'm using lookup tables to determine these). So if I have a blank in the B 'name' column, then all the results in E '***' column are N/A.

Is there a way to get the formula to ignore these N/A results?

Cheers

Thanks for that, I went with the latter.

I tried to use the same principle with E column,

=IF(H37="","",IF(H37="M",$E$1/D37,$E$2/D37))

so I now get blanks, but this hasn't solved the problem with P and X column array formula results.

I get the impression I'm missing something very simple with this that would get it to work.

Change column E

=IF(H5="",0,IF(H5="M",$E$1/D5,$E$2/D5))

Ah I see, thankyou very much.