Please help. Yacht Race Handicap Median?

[Cathy]

Hi there. Would appreciate if somebody could provide some assistance
with this one

http://www.greenwichyachtclub.co.uk/racing/051218.xls

Yes it does contain macros, nothing bad but you do not have to enable
macros if opening spreadsheet.

The problem I am having is located in H3 and I3

Excel does provide a Median Function and this so far appears to work
perfectly in I3. I am having a problem getting my head round a way to
get H3 to provide the median for the corresponding boat handicaps as
those used for the median in I3

eg. With the Spreadsheet as it is, the Median is taken as the average of
1410 and 1483 giving 1447.
therefore H3 should give the average of 730 and 733 therefore 732 as per
spreadsheet and not 734 as Median Function would give as our rules
clearly state the median must be from the same two boats.

Remember, when there are 5 boats, there will be only one number and then
the corresponding number can more easily used as no average will exist.

Any help to address this problem will be greatly appreciated.

Kind regards
Cathy

 

[Peter T]

Hi Cathy,

You have something like this in say A1:B4
1 1
6 7
3 8
4 9

The median in col-B is (7+8)/2
Col A is un-ordered (as in your sheet) and the median is (3+4)/2 whereas you
want the average of the corresponding values 7/8, ie (6+3)/2

So what you want is the average of offset's 2 & 3 down in col A

Referring to your sheet I defined a dynamic name for your formulas in
"Handicap", Ctrl-F2

"Handicap" refersto =OFFSET(Race!$G$5,0,0,COUNTA(Race!$D$5:$D$100),1)

and for your average formula
=(OFFSET(G5,INT((COUNTA(Handicap)+0.5)/2),,1)+OFFSET(G5,INT((COUNTA(Handicap
)-0.5)/2),,1))/2

There's probably a simpler way of doing that.

I don't however follow the logic of averaging the median of these unsorted
numbers, but it's been a while since I last raced on water!

Regards,
Peter T

PS, you might consider similar dynamic ranges for other parts of this sheet.
The counta is based on values in the main column D

 

[Cathy]

Thank you very much, just what I was looking for. Much appreciated

Kind regards
Cathy