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


--
Regards,
Tom Ogilvy


"Cathy" wrote in message
...
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" wrote in message
...
This formula reproduced all your values:


=IF(COUNTA(B4:S4)=5,SUMPRODUCT(LARGE(OFFSET(A4,0, SMALL(IF(B4:S40,COLUM
N(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.

--
Regards,
Tom Ogilvy


"Cathy" wrote in message
...
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