Help with Spreadsheet for Handicap

[Cathy]

I have a small spreadsheet downloadable from:

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

I am trying to find a way to calculate the value in column A

This value has to be the average of the first 5 values in the same row
excluding the highest value of 5

In my spreadsheet you will note that all rows with less than 5 numbers
merely take the average of all the numbers in that row

The ones that have a yellow cell under "Value" almost has a working
formula but I am trying to figure out how to get Column U to
automatically show the reference to the 5th value.

Cells in blue are the cells that are averaged out. Cells in red are
values excluded.

Perhaps there is a much easier way of doing this.

Thanks in advance for any assistance

Regards
Cathy

 

[Tom Ogilvy]

This formula reproduced all your values:

=IF(COUNTA(B4:S4)>=5,SUMPRODUCT(LARGE(OFFSET(A4,0,SMALL(IF(B4:S4>0,COLUMN(B4
:S4)-1),{1,2,3,4,5})),{2,3,4,5}))/4,SUM(B4:S4)/COUNT(B4:S4))

Except row 33 where you have t33 in U33 and it should be s33. Then the
above formula matches the values produced by all rows.

The formula must be entered with Ctrl+Shift+Enter.

 

[Cathy]

Brilliant.

Thank you very much, I was getting my head in a twist over that. Will
try to figure the formula out now. Never heard of using Ctrl+Shift+Enter
in Excel before and would like to find out the purpose of this. WIll do
a bit more reading up.

PS. Yes I did make a mistake in row 33.

Regards
Cathy

 

[Tom Ogilvy]

It is called an array formula.

when properly entered with Ctrl+Shift+Enter, then if you look at it in the
formula bar it will appear to be enclosed in curly brackets

{=formula}

to indicate that it is an array formula. (Excel displays the brackets as
indicators - they are not really there)

A simple example is a conditional sum - get the sum of payments by the Tom
who Lives in London

{=Sum((A1:A10="Tom")*(B1:B10="London")*C1:C10)}

would sum the cells in C1:C10 where both the corresponding cells in columns
A and B equaled to Tom and London respectively

See Chip Pearson's page on this:
http://www.cpearson.com/excel/array.htm

 

[Cathy]

Think this is all just slightly out of my league.

Your formula works perfectly, however I am struggling to find a way to
use conditional formatting to highlight the 4 numbers used in
calculation in light blue and the number discarded in red.

I have made the changes as per your formula and reloaded the sheet

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

I guess I am looking for column t to show the column number of the 5th
value counting from the left but if there is an easier way then I will
be all ears.

Thanks again in advance for any assistance.

Kind regards
Cathy

 

[Roger Govier]

Hi Cathy

To return the column number of the highest value in the row it is
=INDEX($B$3:$S$4,1,MATCH(MAX(B4:S4),B4:S4))+1

For your conditional formatting, use the dropdown to select Formula Is
rather than Cell Value
Condition 1, Formula Is =B4=MAX($B4:$S4)
Condition 2, Formula Is =B4>0

 

[Tom Ogilvy]

=IF(COUNT(B4:S4)>=5,LEFT(ADDRESS(ROW(),SMALL(IF(B4:S4>0,COLUMN(B4:S4)),5),4,
TRUE),1),"")

Entered with Ctrl+Shift+Enter in T4, then drag fill downthe column.

 

[Tom Ogilvy]

Just for information, she doesn't want the highest value in the row.

 

[Roger Govier]

Hi Tom

My apologies.
I just looked at Cathy's sheet on her website, and saw she had used the
following as a test in her Conditional Formatting
=MAX($B4:INDIRECT(CONCATENATE($T4,ROW())))
This led me to think she was looking for the highest value in the row.
I have now read the rest of the thread, and I can see that she was
asking for something different.