Help with Spreadsheet for Handicap

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cathy - 22 Jan 2006 00:18 GMT

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

Reply to this Message

Tom Ogilvy - 22 Jan 2006 01:47 GMT

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.

Signature

Regards,
Tom Ogilvy

> I have a small spreadsheet downloadable from:
>
[quoted text clipped - 21 lines]
> Regards
> Cathy

Reply to this Message

Cathy - 22 Jan 2006 09:03 GMT

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

> This formula reproduced all your values:

=IF(COUNTA(B4:S4)>=5,SUMPRODUCT(LARGE(OFFSET(A4,0,SMALL(IF(B4:S4>0,COLUM
N(B4

> :S4)-1),{1,2,3,4,5})),{2,3,4,5}))/4,SUM(B4:S4)/COUNT(B4:S4))
>
[quoted text clipped - 28 lines]
> > Regards
> > Cathy

Reply to this Message

Tom Ogilvy - 22 Jan 2006 09:32 GMT

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

Signature

Regards,
Tom Ogilvy

> Brilliant.
>
[quoted text clipped - 47 lines]
> > > Regards
> > > Cathy

Reply to this Message

Cathy - 22 Jan 2006 10:13 GMT

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

Reply to this Message

Roger Govier - 22 Jan 2006 11:44 GMT

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

Signature

Regards

Roger Govier

> Think this is all just slightly out of my league.
>
[quoted text clipped - 14 lines]
> Kind regards
> Cathy

Reply to this Message

Tom Ogilvy - 22 Jan 2006 12:12 GMT

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

Signature

Regards,
Tom Ogilvy

> Hi Cathy
>
[quoted text clipped - 24 lines]
> > Kind regards
> > Cathy

Reply to this Message

Roger Govier - 22 Jan 2006 14:24 GMT

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.

Signature

Regards

Roger Govier

> Just for information, she doesn't want the highest value in the row.
>
[quoted text clipped - 30 lines]
>> > Kind regards
>> > Cathy

Reply to this Message

Tom Ogilvy - 22 Jan 2006 12:09 GMT

=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.

Signature

Regards,
Tom Ogilvy

> Think this is all just slightly out of my league.
>
[quoted text clipped - 14 lines]
> Kind regards
> Cathy

Reply to this Message